CODEWITHSUNDEEP
sqlsql-server
Niels
Niels

Reputation: 21

SQL: selecting rows where column value changed last time

I need to get the latest date where number is changed I have this SQL statement

Select  
    a.group, a.date a.number 
From 
    xx.dbo.list a
Where 
    a.group in ('10, '10NC', '210')
    And a.date >= '2018-06-01'
    And a.number > 0
    And a. number <> (Select Top 1 b.number
                      From  xxx.dbo.list b
                      Where b.group  = a.group
                        And b.date >= '2018-06-01'
                        And b.number > 0
                        And b.date < a.date
                      Order by b.date desc)
order by a.date desc

I have a table that looks like this

Group   date        Number  
--------------------------
10      2018-02-06  4
10      2018-04-06  4
10      2018-06-12  4
10NC    2018-02-06  68
10NC    2018-04-06  35
10NC    2018-06-11  35
10NC    2018-06-12  68
10NC    2018-06-13  35
210     2018-06-02  94
210     2018-06-04  100
210     2018-06-06  100
210     2018-06-07  93

I get this output now, but I only want to get the rows with X

Group   date        Number
------------------------------
10NC    2018-06-12  68  
10NC    2018-06-13  35   X
210     2018-06-04  100  
210     2018-06-07  93   X

Can anyone help?

Upvotes: 1

Views: 159

Answers (2)

Mallikh
Mallikh

Reputation: 16

Is this what is Expected?

DECLARE @List TABLE ([Group] VARCHAR(100), [Date]  DATE, Number INT)

INSERT INTO @List  
SELECT '10','2018-02-06',4
UNION ALL
SELECT '10','2018-04-06',4
UNION ALL
SELECT '10','2018-06-12',4
UNION ALL
SELECT '10NC','2018-02-06',68
UNION ALL
SELECT '10NC','2018-04-06',35
UNION ALL
SELECT '10NC','2018-06-11',35
UNION ALL
SELECT '10NC','2018-06-12',68
UNION ALL
SELECT '10NC','2018-06-13',35
UNION ALL
SELECT '210','2018-06-02',94
UNION ALL
SELECT '210','2018-06-04',100
UNION ALL
SELECT '210','2018-06-06',100
UNION ALL
SELECT '210','2018-06-07',93


;WITH CTE AS
(
SELECT 
    *
    ,RN = ROW_NUMBER() OVER (Partition by [Group] ORDER BY [DATE] DESC)
FROM @List
WHERE 
 [Date] >= '2018-06-01'
 AND [Group] in ('10', '10NC', '210') 
 And Number > 0
 )
 SELECT * FROM CTE WHERE RN = 1

Note: I am posting it directly in answer as i don't have enough reputation to ask questions in comments.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You would use lag():

select a.*
from (select  a.group, a.date,  a.number, lag(a.number) over (partition by group order by date) as prev_number
      From xx.dbo.list a
      where a.group in ('10', '10NC', '210') And
            a.date >= '2018-06-01' And
            a.number > 0
    ) a
where prev_number <> number;

Upvotes: 2

Related Questions