Select limited number of rows based on unique column value

Question

I have a table in the following format

ID SOURCE_ID
 1 1
 2 1
 3 1
 4 2
 5 3
 6 3
 7 4
 8 4
 9 4
10 4
11 4
12 1
13 1
14 3
15 3
16 3
17 3
18 2
19 2

I want to be able to select 5 records MAX for each unique source_id.

So I should end up having returned 5 rows for source_id = 1, 5 rows for souce_id = 2, and so on.

Any ideas? Thank you in advance.

Possible duplicate of [How do I limit the number of rows per field value in SQL?](https://stackoverflow.com/questions/10421807/how-do-i-limit-the-number-of-rows-per-field-value-in-sql) — Iurii Drozdov, Oct 25 '17 at 11:41
@P.Salmon the order is not important. What is important is the fact that I will need maximum 5 records (or less if not more present within that table) for source_id = 1, maximum 5 for source_id = 2 and so on. But I don't want to specify hardcoded these values, I want to be able to select them within the same query — shAkur, Oct 25 '17 at 11:46

score 0 · Accepted Answer · answered Oct 25 '17 at 11:56

E.g.:

SELECT id
     , source_id 
  FROM 
     ( SELECT id
            , source_id
            , CASE WHEN @prev = source_id THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev := source_id prev 
         FROM my_table
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY source_id
            , id
     ) x 
 WHERE i <=5 
 ORDER 
    BY id;

Select limited number of rows based on unique column value

1 Answers1