Select first two distinct occurrences and return rowid

Question

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.

Would you please show the expected output that corresponds to your sample data? — GMB, Sep 18 '19 at 21:20
What do you mean by `row_id`? That is not a column in your data. — Gordon Linoff, Sep 18 '19 at 21:53
By `row_id` I mean a row identificator that is held by oracle and can help me to implement a paging function in C++. — Evelek Eve, Sep 18 '19 at 21:59

score 1 · Answer 1 · answered Sep 18 '19 at 21:25

Here's one option:

SQL>  set ver off
SQL>
SQL> with test (room_id, name, time) as
  2    (select 1, 'Kate'   , to_date('18.09.2019 10:00', 'dd.mm.yyyy hh24:mi') from dual union all
  3     select 1, 'Michael', to_date('18.09.2019 12:00', 'dd.mm.yyyy hh24:mi') from dual union all
  4     select 1, 'George' , to_date('18.09.2019 14:00', 'dd.mm.yyyy hh24:mi') from dual union all
  5     select 2, 'Tom'    , to_date('17.09.2019 09:00', 'dd.mm.yyyy hh24:mi') from dual union all
  6     select 2, 'Ben'    , to_date('17.09.2019 15:00', 'dd.mm.yyyy hh24:mi') from dual union all
  7     select 3, 'Joerge' , to_date('16.09.2019 17:00', 'dd.mm.yyyy hh24:mi') from dual
  8    ),
  9  rn_room as
 10    (select room_id,
 11            row_number() over (order by room_id) rnr
 12     from (select distinct room_id from test)
 13    ),
 14  rn_time as
 15    (select room_id, name, time,
 16            row_number() over (partition by room_id order by time desc) rnt
 17     from test
 18    )
 19  select r.room_id, t.name, t.time
 20  from rn_time t join rn_room r on r.room_id = t.room_id
 21  where r.rnr <= &N
 22    and t.rnt = 1;
Enter value for n: 2

   ROOM_ID NAME    TIME
---------- ------- ----------------
         1 George  2019-09-18 14:00
         2 Ben     2019-09-17 15:00

SQL>

rn_room sorts rooms
rn_time sorts names per each room by time
the final select joins those two data sets

thanks for the sample data of the demo Littlefoot +1 :) – Barbaros Özhan Sep 18 '19 at 21:32 — Barbaros Özhan, Sep 18 '19 at 21:32

score 1 · Accepted Answer · edited Sep 18 '19 at 22:05

1

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.

edited Sep 18 '19 at 22:05

GMB

216,147
25
84
135

answered Sep 18 '19 at 21:26

Barbaros Özhan

59,113
10
31
55

1

It works for me immediately by copy-paste to my Microsoft SQL Server Management Studio, so thanks. – Evelek Eve Sep 18 '19 at 22:02
Hmm, but it is not exactly a correct solution. What if values in column room_id are: first_room, second_room, third_room. How would you find first N distincts, then? – Evelek Eve Sep 18 '19 at 22:20
Excuse me, I couldn't understand what you mean @EvelekEve . By the way you need to return only one row(as in the desired output case), don't you? – Barbaros Özhan Sep 18 '19 at 22:27
Current values in column room_id are 1, 2, 3. Let's replace them with first_room, second_room, third_room. How to find the first N distinct room_ids? – Evelek Eve Sep 18 '19 at 22:28
interesting :) , the question suggests they're are integer. Moreover does the subtitution variable N will be string also, doesn't it? @EvelekEve – Barbaros Özhan Sep 18 '19 at 22:31
Yeah, question suggests they are integer, but it is only an example. In real database they are varchar2. The subtitution variable N is an integer. – Evelek Eve Sep 18 '19 at 22:34
@EvelekEve do you mean [this](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=37beaec7743bf08a71910f18808ebf64) ? or you need impossible :) ? string `twentysecond_room` contains `second` too ... – Barbaros Özhan Sep 18 '19 at 22:35
I think this is in the scope of another question @EvelekEve – Barbaros Özhan Sep 18 '19 at 22:41

Select first two distinct occurrences and return rowid

2 Answers2