Select first two distinct occurrences and return rowid

I have a table like this:

room_id | name    | time
1       | Kate    | 2019-09-18 10:00:00.000
1       | Michael | 2019-09-18 12:00:00.000
1       | George  | 2019-09-18 14:00:00.000
2       | Tom     | 2019-09-17 09:00:00.000
2       | Ben     | 2019-09-17 15:00:00.000
3       | Joerge  | 2019-09-16 17:00:00.000

I want to select the first N distinct room_ids and a row_id of the last one. For example I want to select first two distinct rooms_ids and return a row_id of the last one. The result should be:

room_id
1
2

The row_id should be identified with this record:

2       | Ben     | 2019-09-17 15:00:00.000

I have already written my SQL statement but it does not work:

SELECT distinct room_id
  FROM (
    SELECT DISTINCT room_id, time,
    rn = ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY room_id, time)
    FROM tab
  ) AS sub
  WHERE rn <= N;

‘N’ is a value of how many distinct room_ids I want to select. I also don’t know how to return the row_id of the last record.

Answer

You can try in this manner :

with t1 as
(
  select t.*,
         row_number() over (partition by room_id order by room_id, time desc) as rn
    from tab t
) 
select room_id, name, time
from t1
where rn = 1 and room_id = N

Demo

The important point of interest is considering order by time desc within the row_number() analytic function and grabbing the rn = 1 for the outer query.

Advertisement

Answer