Get top n records with grouped table

Question

I have an order table like this:

|---------------------|------------------|
|      created_at     |     user_id      |
|---------------------|------------------|
| 2019-10-14 01:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 02:01:59 |       2          |
|---------------------|------------------|
| 2019-10-13 00:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 03:01:59 |       3          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       2          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       1          |
|---------------------|------------------|
| 2019-10-10 14:01:59 |       3          |
|---------------------|------------------|
| 2019-10-11 20:01:59 |       2          |
|---------------------|------------------|
| 2019-10-09 12:01:59 |       3          |
|---------------------|------------------|

I grouped my table by this query to get top newest order each user:

select created_at, user_id
from orders
order by user_id, created_at desc

I got these records:

|---------------------|------------------|
|      created_at     |     user_id      |
|---------------------|------------------|
| 2019-10-14 01:01:59 |       1          |
|---------------------|------------------|
| 2019-10-13 00:01:59 |       1          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       1          |
|---------------------|------------------|
| 2019-10-14 02:01:59 |       2          |
|---------------------|------------------|
| 2019-10-12 23:01:59 |       2          |
|---------------------|------------------|
| 2019-10-11 20:01:59 |       2          |
|---------------------|------------------|
| 2019-10-14 03:01:59 |       3          |
|---------------------|------------------|
| 2019-10-10 14:01:59 |       3          |
|---------------------|------------------|
| 2019-10-09 12:01:59 |       3          |
|---------------------|------------------|

Now I want it return only 2 first records each group. How I can do that?

Thank you!

Ed Bangga · Accepted Answer · 2019-10-14T08:26:58.547

1

if you are using mysql 8.0 or higher you can use row_number() over partition by..

select t1.created_at, t1.user_id from (
select row_number() over (partition by user_id order by created_at desc) rn, created_at, user_id
from orders) t1 where t1.rn <=2

using mysql versions 5.7 and below

SELECT t1.created_at, t1.user_id 
FROM (SELECT 
      @row_number:=CASE
          WHEN @varId = user_id 
            THEN 
                @row_number + 1
            ELSE 
                 1
          END AS rn,
      @varId:=iuser_id user_id,
      created_at
    FROM
        orders,
        (SELECT @varId:=0,@row_number:=0) as t
    ORDER BY 
        user_id asc, created_at desc) t1 
WHERE t1.rn <= 2

edited Oct 14 '19 at 08:26

answered Oct 14 '19 at 07:26

Ed Bangga

12,879
4
16
30

I got error Unknown column 't1.rn' in 'where clause' – Tomato Oct 14 '19 at 07:30
updated. try again – Ed Bangga Oct 14 '19 at 07:34
Sorry for annoying but anyway for me to do the same like this in MySQL 5.7 – Tomato Oct 14 '19 at 07:51
it's only give my 2 first row of whole result. Not same like your row_number() query sir. – Tomato Oct 14 '19 at 08:04
try above query for 5.7 – Ed Bangga Oct 14 '19 at 08:27

Get top n records with grouped table

1 Answers1