Select Rows Where Column Value Has Changed

Select rows where column value has changed

I think this is what you're after:

;WITH x AS
(
  SELECT value, time, rn = ROW_NUMBER() OVER 
  (PARTITION BY Value ORDER BY Time)
  FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;

This may be slow if the resultset is large and there isn't a good supporting index...

EDIT

Ah, wait a second, the values go up and down, not just up... if that is the case you can try this much slower approach:

DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
  FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;

Results:

value  time
-----  -----------------------
1      2012-06-15 20:03:43.000
2      2012-06-15 20:04:03.000
3      2012-06-15 20:04:13.000
2      2012-06-15 20:04:28.000

SQL: selecting rows where column value changed from previous row

SELECT a.*
FROM tableX AS a
WHERE a.StatusA <>
      ( SELECT b.StatusA
        FROM tableX AS b
        WHERE a.System = b.System
          AND a.Timestamp > b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      ) 

But you can try this as well (with an index on (System,Timestamp):

SELECT System, Timestamp, StatusA, StatusB
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged ;

return row where column value changed from last change

use lag() to find the last change (order by RecordAddedDate desc) in PartNumberID.

cumulative sum sum(isChange) to group the related rows under same group no. grp = 0 with be the rows of the last change

To get the min - RecordAddedDate, use row_number()

with 
cte1 as
(
    select *,
           isChange = case when PartNumberID
                           =    isnull(lag(PartNumberID) over (partition by Location 
                                                                   order by RecordAddedDate desc), 
                                       PartNumberID)
                           then 0
                           else 1
                           end
    from   mytable
),
cte2 as
(
    select *, grp = sum(isChange) over (partition by Location order by RecordAddedDate desc)
    from   cte1
),
cte3 as
(
    select *, rn = row_number() over (partition by Location order by RecordAddedDate)
    from   cte2 t
    where  t.grp = 0
)
select *
from   cte3 t
where  t.rn = 1

db<>fiddle demo

Select rows where value changed in column

In SQL Server 2012+, you would use lead():

select t.*
from (select t.*,
             lead(charge_code) over (partition by account order by postingdate) as next_charge_code
      from t
     ) t
where charge_code <> next_charge_code;

In earlier versions of SQL Server, you can do something similar with apply.

Select and join rows when column value changes per group

If you are using MySQL 8, Window functions can help. https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

You can partition your results by device and test, and add a column that is the previous value of the result, then use the last row where the result differs from the previous value.

The following query creates a new column in your results with previous_value

SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)

Yields the result:

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             1 |         1 | A    | PASS   | NULL           |                   NULL |
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             2 |         2 | B    | FAIL   | NULL           |                   NULL |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
|             5 |         2 | B    | PASS   | PASS           |                      4 |
+---------------+-----------+------+--------+----------------+------------------------+

Which is a big part of the battle. Now we need to take that result and find the row for each device/test pair with the highest assessment_id, where result != previous_value.

The window is calculated after GROUP BY, ORDER BY, and even HAVING, so there's not much more that can be done in that query (that I have thought of) to narrow it down to the only the most recent entries for each device/test pair. So the above will have to be a subquery to get the final result.

Note: I am going to assume that if the result never changes, you want to show the first time that result was recorded. In other words, you want to count results with previous_value = NULL as a transition.

Here's a query that lists all the times the test result from a device/test pair changes:

SELECT * FROM 
(SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY `device_id`, `test` ORDER BY `assessment_id`)
) AS t
WHERE result != `previous_value` OR `previous_value` IS NULL

gets the result (I left out previous_assesssment_id and the others for space):

+---------------+-----------+------+--------+----------------+
| assessment_id | device_id | test | result | previous_value |
+---------------+-----------+------+--------+----------------+
|             1 |         1 | A    | PASS   | NULL           |
|             3 |         1 | A    | FAIL   | PASS           |
|             2 |         2 | B    | FAIL   | NULL           |
|             4 |         2 | B    | PASS   | FAIL           |
+---------------+-----------+------+--------+----------------+

EDIT

That's the answer to the question. If the first time the value is set is not of interest, just delete the OR part of the WHERE clause. There rest of this answer is because I convinced myself the problem was to get the MOST RECENT time the value flipped. I'm leaving it here, but only for interest.

Carrying On

This is all the times the outcome was different than previous, plus the first time a result was recorded. Almost there.

It would be tempting at this point to add another window in the outer query to aggregate the rows from above and identify the correct rows. But at least in MySQL 8, nested windows are not supported.

But given that result, we can create a query using MAX() and GROUP BY that gives the assessment_ids of all the rows we ultimately want:

SELECT MAX(assessment_id)
FROM (
    SELECT 
       assessment_id,
       device_id,
       test,
       result,
       LAG (result) over w as `previous_value`,
       LAG (assessment_id) over w as `previous_assessment_id`
    FROM assessments join results using(assessment_id)
    WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
) AS t

where result != previous_value OR previous_value IS NULL
GROUP BY device_id, test

Which yields:

+--------------------+
| MAX(assessment_id) |
+--------------------+
|                  3 |
|                  4 |
+--------------------+

Now we know exactly which rows we need; but we built all that data about the previous values, and now we need a way to join the result of that query with the result of the subquery.

Happily, MySQL 8 has a way to stash a query and use it multiple times, called Common Table Expressions, that use the WITH clause docs here. So we can create the table with all the fun data, then use it as a subquery to get the id's we ultimately want, and then join that right back with the results we just created:

WITH
  transitions AS (SELECT 
    assessment_id,
    device_id,
    test,
    result,
    LAG (result) over w as `previous_value`,
    LAG (assessment_id) over w as `previous_assessment_id`
FROM assessments join results using(assessment_id)
WINDOW w AS (PARTITION BY device_id, test ORDER BY assessment_id)
)

SELECT transitions.*
FROM transitions
JOIN (
    SELECT MAX(assessment_id) as assessment_id
    FROM transitions
    WHERE result != previous_value OR previous_value IS NULL
    GROUP BY device_id, test
) AS t2 using (assessment_id)

Giving us the final answer (with the other columns you can fill in):

+---------------+-----------+------+--------+----------------+------------------------+
| assessment_id | device_id | test | result | previous_value | previous_assessment_id |
+---------------+-----------+------+--------+----------------+------------------------+
|             3 |         1 | A    | FAIL   | PASS           |                      1 |
|             4 |         2 | B    | PASS   | FAIL           |                      2 |
+---------------+-----------+------+--------+----------------+------------------------+

The first part creates a data set that includes all the information about what came before each test. Then we write a query that gets the id's of the interesting rows in that query, then we join back to the original data set to fill in all the columns.

Selecting rows where a value has changed

select p1.date_entered, 
       p1.WholeSale,
       p1.Volume,
       p1.Clearance 
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 * 
        FROM pricetable p2 
            where p1.product = p2.product 
                and p2.date_entered < p1.date_entered 
                    order by p2.date_entered desc) CA
where p1.product = 'TANGO'
  and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc

How to select only last X values where column value changed

You can use Lag function and Cte table as follows:

WITH Cte AS (
    SELECT *,
           LAG(Note,1) OVER(PARTITION BY Item_ID ORDER BY Id) PrevNote
    FROM [dbo].[LOGTABLE]
),   
Final AS(
    SELECT *,
           RANK() OVER (PARTITION BY ITem_ID ORDER BY ID DESC) rnk 
    FROM Cte AS c
    WHERE Note <> ISNULL(PrevNote,'')
)
SELECT * 
FROM final
WHERE rnk <= 5
ORDER BY ITem_ID

It gives you result below:

ID  ITEM_I  DATETIME    TYPE      NOTE  PrevNote    rnk
10  167 2021-06-15 17:37:15.000 0   T   F            1
8   167 2021-06-15 17:24:47.000 0   F   T            2
2   167 2021-06-15 16:47:23.000 0   T   NULL         3
9   168 2021-06-15 17:26:02.000 0   F   T            1
7   168 2021-06-15 17:13:25.000 0   T   F            2
3   168 2021-06-15 16:48:11.000 0   F   NULL         3

It returns Maximum 5 rows for each Item_Id That has changed

Selecting rows when there is a change in the value of column from the previous row

You can use a CTE with a CASE and LAG and LEAD to calculate what rows to select. this will work for versions 2012 and higher:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    Name varchar(4),
    [Status] int,
    [Timestamp] date
)

INSERT INTO @T VALUES
('Joe', 1, '2015-11-12'),   
('Joe', 2, '2015-11-13'),
('Joe', 2, '2016-12-14'),
('Joe', 2, '2016-12-15'),
('Paul' ,1, '2015-08-16'),
('Paul' ,1, '2015-08-17'),
('Paul' ,3, '2015-08-18'),
('Paul' ,3, '2015-08-19'),
('Mark' ,2, '2015-09-20'),
('Mark' ,2, '2015-09-25'),
('Mark' ,2, '2015-09-26'),
('Mark' ,3, '2015-10-27')

The cte - Note that I use both lag and lead inside the case expression.

;WITH CTE AS
(
    SELECT  Name,
            [Status],
            [Timestamp],
            CASE WHEN LAG([Status]) OVER(PARTITION BY Name ORDER BY [Timestamp]) <> [Status] OR
                      LEAD([Status]) OVER(PARTITION BY Name ORDER BY [Timestamp]) <> [Status] THEN 
                1
            END As Filter
    FROM @T
)

The query:

SELECT  Name,
        [Status],
        [Timestamp]
FROM CTE
WHERE Filter = 1

Results:

Name    Status  Timestamp
Joe     1       12.11.2015 00:00:00
Joe     2       13.11.2015 00:00:00
Mark    2       26.09.2015 00:00:00
Mark    3       27.10.2015 00:00:00
Paul    1       17.08.2015 00:00:00
Paul    3       18.08.2015 00:00:00

See a live demo on rextester

Select rows where column value has changed With agreegation

Use window functions - first compute flag whose value is 1 on every seller change, then compute group numbers as sum of those flags from the beginning so the first group has value 1, second group 2 etc..., then group by group number and compute what you need.

select min(seller) as Seller
     , avg(price) as Avg_Price
     , min(date) as From_Date
     , max(date) as To_Date
from (
  select seller, price, date, sum(seller_chaned) over (order by date) as grp
  from (
    select seller, price, date
         , case when lag(seller) over (order by date) != seller then 1 else 0 end as seller_changed
    from t
  )
)
group by grp
order by grp

(Note I composed query just in my head so there might be typos or off-by-one errors but I hope the idea is obvious. It is better to provide dbfiddle with sample data.)

---

Related Topics