-1

Suppose i have a user_data table which contains multiple records of users.

id       user_id  col1    col2  created_at
1        1        abc     x     2019-01-04
2        2        def     x     2019-02-01
3        3        ghi     x     2019-03-05
4        2        jkl     x     2019-07-07
5        2        mno     x     2019-06-01
6        3        pqr     x     2019-07-11

Now i want to select top 10 latest records per user in a single query.

John Wick
  • 29
  • 1
  • 1
  • 7

1 Answers1

1

Use row_number():

select ud.*
from (select ud.*,
             row_number() over (partition by user_id order by created_at desc) as seqnum
      from user_data ud
     ) ud
where seqnum <= 10;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ok. As inner query is fetching all the records first, will it affect the performance if **user_data** table is large. If yes then should i fetch top 10 records for each user separately by using **limit** clause inside a loop. – John Wick May 21 '19 at 10:48
  • @JohnWick . . . This should be faster than a look that executes separate queries. – Gordon Linoff May 21 '19 at 11:03