Oracle SQL: Limit row numbers in a query with association table

Question

This request is extracted from a complex one.

In this example I have two data tables: user_table, ref, and one association table: user_ref_asso.

The schema, and test query are here : http://sqlfiddle.com/#!4/0a302/18

I try to limit the number of USER_TABLE results using "where rownum < X" but it limits the total results (user + ref).

My current query is:

select * from
(SELECT u.user_id, r.ref_id, u.name, r.ref
 FROM user_table u
 INNER JOIN user_ref_asso ur
 ON ur.user_id = u.user_id
 INNER JOIN REF r
 ON r.ref_id = ur.ref_id
 order by u.user_id, r.ref_id)
WHERE rownum <= 2;

For example, if the result without row limits is:

USER REF
1    1
1    2
2    1
2    2
3    1
3    2

If I set the row number limit to 2, the expected result would be (2 distinct users):

USER REF
1    1
1    2
2    1
2    2

But in my case the result is (2 results):

USER REF
1    1
1    2

How to limit row numbers on distinct user_id column ?

Answer 1

Use Analytical function to achieve this:

select user_id, ref_id, name, ref from
(SELECT u.user_id, r.ref_id, u.name, r.ref, dense_rank() over (order by u.user_id) rn
   FROM user_table u
  INNER JOIN user_ref_asso ur
        ON ur.user_id = u.user_id
  INNER JOIN REF r
     ON r.ref_id = ur.ref_id
  order by u.user_id, r.ref_id)
WHERE rn <= 2;

Output:

| USER_ID | REF_ID |  NAME |  REF | RN |
|---------|--------|-------|------|----|
|       1 |      1 | Name1 | Ref1 |  1 |
|       1 |      2 | Name1 | Ref2 |  1 |
|       2 |      1 | Name2 | Ref1 |  2 |
|       2 |      2 | Name2 | Ref2 |  2 |

sql Fiddle