Get data by applying conditions within a column-CodePudding

I am writing a query to get two set of data. Here is how data looks like after order it by desc

Select RecordId,Tab, Status, CreatedDate 
from Table1
Order By Tab, CreatedDate Desc

RecordId	Tab	Status	CreatedDate
1	100	1	2021-10-13 08:15:02.154
1	100	2	2021-10-13 08:04:05.456
2	200	2	2021-10-13 08:13:00.198
2	200	1	2021-10-13 08:07:09.052
2	200	3	2021-10-13 08:04:03.306
3	300	1	2021-10-13 08:12:02.051
3	300	2	2021-10-13 08:08:05.158
3	300	3	2021-10-13 08:02:01.756
4	400	3	2021-10-13 08:14:01.356
4	400	1	2021-10-13 08:09:05.753
4	400	2	2021-10-13 08:05:06.152

First Set : Only those RecordId, which has current Status as 1 (In our case RecordId 1 and 3)

Second Set : Only those RecordId, Where it has status as 1 earlier but got changed currently (In our case RecordId 2 and 4)

Please help me in writing a query on this.

CodePudding user response：

Maybe it is what you are looking for:

Select RecordId as FirstSet
from Table1 t1
where t1.Status = 1 and not exists (
    select * from Table1 t2 where t2.RecordId = t1.RecordId and t2.CreatedDate > t1.CreatedDate
)

Select RecordId as SecondSet
from Table1 t1
where t1.Status = 1 and exists (
    select * from Table1 t2 where t2.RecordId = t1.RecordId and t2.CreatedDate > t1.CreatedDate
)

CodePudding user response：

You can use row-numbering and a windowed count for these queries

SELECT
  RecordId,
  Tab,
  Status,
  CreatedDate 
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY RecordId ORDER BY CreatedDate DESC)
    FROM Table1
) t
WHERE rn = 1 AND Status = 1
ORDER BY Tab, CreatedDate Desc;

SELECT
  RecordId,
  Tab,
  Status,
  CreatedDate 
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY RecordId ORDER BY CreatedDate DESC),
      has1 = COUNT(CASE WHEN Status = 1 THEN 1 END) OVER (PARTITION BY RecordId)
    FROM Table1
) t
WHERE rn = 1 AND Status <> 1 AND has1 = 1
ORDER BY Tab, CreatedDate Desc;

db<>fiddle