Select data with limit on each column value

Question

Consider the following table:

id int primary_key
user_id int
type enum('type1', 'type2', 'type3')

with actual state:

id user_id type
1  2       type1
2  2       type1
3  2       type1
4  2       type1
5  2       type2

I'd like to write a select, which returns rows with id: 1,2,3,5; I'd like to select all rows for user_id = 2, while selecting only max 3 of each type. Row with id=4 would not be selected, because it would have already found 3 rows with type1.

One way to do it is to create 3 seperate query for each value of type and then making an UNOIN of them. Is there a better/more efficient way?

have a look at this question: http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — fthiella, Mar 19 '15 at 13:58

score 0 · Answer 1 · answered Mar 19 '15 at 14:42

0

Try this:-

SELECT *
FROM your_table yt
WHERE (SELECT COUNT(*)
       FROM your_table ty
       WHERE ty.type = yt.type
       AND ty.id <= yt.id
      ) <= 3;

I think this might help you.

answered Mar 19 '15 at 14:42

Ankit Bajpai

13,128
4
25
40

Select data with limit on each column value

1 Answers1