How to extract the first 5 rows for each id user in Oracle?

Question

I have only one table where I want to extract the first 5 rows for each ID_USER.

I execute the following query:

SELECT
      ID_USER as U_ID_USER,
      COUNT(ID_USER) as NUM_TIC
FROM
     TABLE_USERS
GROUP BY ID_USER
ORDER BY ID_USER

Which returns the following information when run:

U_ID_USER   NUM_TIC
16469       34
29012       4
33759       2

Then I want to put each value of ID_USER in the following query for extract only the first 5 rows:

SELECT *
FROM(
SELECT
       DATE,
       ID_USER,
       C1,
       C2,
       C3
FROM 
       TABLE_USERS
WHERE
      ID_USER = '16469'
ORDER BY ID_USER)
WHERE ROWNUM < 6;

For example for the ID_USER "16469" returns the following information when run:

DATE       ID_USER   C1   C2   C3
13/12/17   16469     X    X    X
11/12/17   16469     X    X    X
07/12/17   16469     X    X    X
04/12/17   16469     X    X    X
01/12/17   16469     X    X    X

That I want is an automatic process in PL/SQL or an query that give me an output like this:

DATE       ID_USER   C1   C2   C3
13/12/17   16469     X    X    X
11/12/17   16469     X    X    X
07/12/17   16469     X    X    X
04/12/17   16469     X    X    X
01/12/17   16469     X    X    X
25/12/17   29012     X    X    X
20/12/17   29012     X    X    X
15/11/17   29012     X    X    X
10/11/17   29012     X    X    X
18/12/17   33759     X    X    X
15/12/17   33759     X    X    X

Is it possible to get this output with PL/SQL or with a query?

As a general (and trivial) suggestion - if you want rows from 1 to 5, use `<= 5` rather than `< 6`. The intent is much clearer to someone else who is reading your query and is trying to understand it. — , Aug 27 '18 at 03:45

score 4 · Answer 1 · answered Aug 26 '18 at 21:13

4

This can be done with row_number.

SELECT DATE,ID_USER,C1,C2,C3
FROM (SELECT
       T.*
      ,ROW_NUMBER() OVER(PARTITION BY ID_USER ORDER BY DATECOL DESC) AS RNUM
      FROM TABLE_USERS T
     ) T
WHERE RNUM < 6

answered Aug 26 '18 at 21:13

Vamsi Prabhala

48,685
4
36
58

score 4 · Accepted Answer · answered Aug 26 '18 at 21:13

Use ROW_NUMBER():

SELECT date, id_user, c1, c2, c3
FROM (SELECT u.*,
             ROW_NUMBER() OVER (PARTITION BY id_user ORDER BY date DESC) as seqnum
      FROM table_users u
     )
WHERE seqnum <= 5;

When you use rownum, then it returns that many rows from the result set. ROW_NUMBER() is different. This is a function that enumerates the rows. It starts with "1" for each id_user (based on the PARTITION BY clause). The row with the highest date gets the value of one, the second highest 2, and so on -- based on the ORDER BY clause.

How to extract the first 5 rows for each id user in Oracle?

2 Answers2

Related