CODEWITHSUNDEEP
sql-server
shashwat
shashwat

Reputation: 8024

Select records those are changed in compare to previous record on the basis of just one field

Consider following sample data

| SN |        DateTime       | Status |
|----|-----------------------|--------|
|  1 | '2015-01-01 00:30:00' | OPEN   |
|  2 | '2015-01-01 00:35:00' | OPEN   |
|  3 | '2015-01-01 00:40:00' | CLOSED |
|  4 | '2015-01-01 00:50:00' | OPEN   |
|  5 | '2015-01-01 01:10:00' | OPEN   |
|  6 | '2015-01-01 01:15:00' | CLOSED |
|  7 | '2015-01-01 01:20:00' | CLOSED |
|  8 | '2015-01-01 01:30:00' | OPEN   |
|  9 | '2015-01-01 01:40:00' | OPEN   |
| 10 | '2015-01-01 01:52:00' | OPEN   |
| 11 | '2015-01-01 01:55:00' | CLOSED |
| 12 | '2015-01-01 02:15:00' | OPEN   |
| 13 | '2015-01-01 02:30:00' | OPEN   |

I need to select records where value for column 'Status' is changed from it's previous record. First record should always be returned.

I could do it using For loop in SQL Server but I want a better solution. Is it possible to do it in a single SELECT statement? The query should return rows with serial 1, 3, 4, 6, 8, 11 and 12.

Upvotes: 4

Views: 111

Answers (3)

Arun Gairola
Arun Gairola

Reputation: 884

we can use Union operator and then self join the table on Id + 1 

select * from Delta where id = 1 
UNION 
select  D.*from 
Delta D JOIN Delta DL On D.Id  = Dl.Id +1  
AND
D.Status <> DL.Status

SQLFiddle

Upvotes: 1

Polux
Polux

Reputation: 153

Or without LAG, you can use this query

select * from (SELECT TOP 1 SN, [DateTime], Status from mytable  order by SN) as s
UNION ALL
SELECT T.SN, t.[DateTime], t.Status 
FROM mytable t
    inner join mytable t2
    on t.SN=t2.SN+1 AND t.status <> t2.status

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use LAG to get previous Status value, then use this value in the WHERE clause of an outer query to get what you want:

SELECT SN, [DateTime], Status 
FROM (
   SELECT SN, [DateTime], Status, 
          LAG(Status) OVER (ORDER BY [DateTime]) AS prevStatus
   FROM mytable ) t
WHERE COALESCE(prevStatus, '') <> Status

Demo here

Upvotes: 8

Related Questions