select limited duplicate values from a specific column for multiple values

Question

I will explain my question here...

This is the table:

id | column 1 | column 2
---+----------+---------
1  | green    | 15
2  | green    | 84
3  | green    | 88
4  | red      | 85
5  | red      | 51
6  | red      | 45
7  | red      | 54
8  | blue     | 58
9  | blue     | 58
10 | blue     | 78

Now i only want the most recent 2 green, most recent 2 of red, and most recent 2 of blue.

The output must seem like this:

id | column 1 | column 2
---+----------+---------
2  | green    | 84
3  | green    | 88
6  | red      | 45
7  | red      | 54
9  | blue     | 58
10 | blue     | 78

How can i accomplish this in one mysql statement or is there more i need to do?

Not a complete duplicate, I think, because here exactly 2 results per group are needed, which is definitely much easier than arbitrary N results per group. — mas.morozov, Oct 22 '13 at 14:06

score 0 · Accepted Answer · edited May 23 '17 at 12:28

Let your table name be tbl and column names be id, color, n. One possible solution is like this:

select * from tbl where id in (
 select max(id) from tbl group by color
union all
 select max(id) from tbl t
 where id not in (select max(id) from tbl t1 where t1.color=t.color)
 group by color
)
order by color, id desc

Here it is to test and play around: http://sqlfiddle.com/#!2/453af7/1/0

There also exist several solutions for the case where you need N top elements from each group. I will not in-depth describe them here, not to duplicate this question, but only give a short annotated list with links to SQLfiddle samples. (1) If you have exactly 3 "fixed" groups 'blue','green','red' - you can directly use order by and limit with union all - sample here. (2) Generally you can calculate row ranks inside each group and later use them in where condition, here is ANSI SQL solution (3) Or its MySQL-specific analog. (4) You can use string functions group_concat and find_in_set like shown here, but this is MySQL-specific solution not conforming to good RDBMS usage practices and I would not advise choosing it.

Hi, this works realy well, thank you, but what if i want 3 or 4 items from every group? — Richard, Oct 23 '13 at 07:11
"3 or 4 items from every group" - Added info on this topic to my answer. — mas.morozov, Oct 23 '13 at 14:23

score 0 · Answer 2 · edited May 23 '17 at 11:57

I solved the puzzle with this query stolen from fthiella and tweaked it a little bit:

SELECT
$yourtable.*
FROM
$yourtable INNER JOIN (
SELECT
$limit_item,
GROUP_CONCAT($the_id ORDER BY add_time DESC) grouped_year
FROM
$yourtable
GROUP BY $limit_item) group_max
ON $yourtable.$limit_item = group_max.$limit_item
AND FIND_IN_SET($the_id, grouped_year) <=3

The $limit_item here is the item you want just n times. The $the_id is just the id in your table, i actualy do not know why they use this. The nice part of this query is that you can manualy set the number of the duplicates you want to limit.

The dollars are php syntax.

select limited duplicate values from a specific column for multiple values

2 Answers2