0

I have a table and I want to display only the rows that had a specific column changed order by the date of the row. I have created a little simplified example. In this example, I want to show the first row of each resas_id based on the on_date column and then display every row that has the status_id column changed (we do not care for other columns, in this case schedule_id)

DECLARE @resas TABLE
(resas_id       bigint      null,
 schedule_id    bigint      null,
 status_id      int         null,
 on_date        datetime    null)

INSERT @resas
SELECT 1000,1,1,'2023-05-22 20:58:05.827'

INSERT @resas
SELECT 1000,2,1,'2023-05-22 20:58:06.827'

INSERT @resas
SELECT 1000,1,2,'2023-05-22 20:58:07.827'

INSERT @resas
SELECT 1001,6,4,'2023-05-22 20:58:08.827'

INSERT @resas
SELECT 1001,7,1,'2023-05-22 20:58:09.827'

INSERT @resas
SELECT 1001,9,1,'2023-05-22 20:58:10.827'

The desired output is:

resas_id    schedule_id status_id   on_date
1000        1           1           2023-05-22 20:58:05.827
1000        1           2           2023-05-22 20:58:07.827
1001        6           4           2023-05-22 20:58:08.827
1001        7           1           2023-05-22 20:58:09.827

Thank you

EDIT: I think @lemon nailed it. This seems to work:

;WITH ccl AS (
SELECT
    p.resas_id,
    p.status_id,
    p.on_date,
    ROW_NUMBER() OVER (PARTITION BY p.resas_id, p.status_id ORDER BY p.on_date) AS rows
FROM @resas p)

SELECT * FROM ccl WHERE rows = 1 ORDER BY resas_id,on_date
PanosPlat
  • 940
  • 1
  • 11
  • 29
  • 1
    You need to apply a filter on the `ROW_NUMBER`. Check the accepted answer from the linked page, not only the post itself. – lemon Jun 01 '23 at 13:11

0 Answers0