mysql limit in group by

Question

Say i have this table fields:

Field1  Field2 Field3
a       1      1
a       1      2
a       1      3
a       1      4
a       2      1
a       2      2
a       2      3
a       3      1
b       1      1
b       1      2
b       1      3
b       2      1
c       1      1

and say i need to group by Field1 (because i need to some calucation on the data), is there a way to retrive only the first 2 elements grouped by Field2 (sorted desc)?

So for this example i'd like to retrive:

a,3,1
a,2,3
a,2,2
a,1,4
a,1,3
b,1,2
b,1,1
b,2,1
c,1,1

would be something like

SELECT field1, 
       Sum(field2), 
       Sum(field3) 
FROM   table t1 
WHERE  t1.Field1 IN (SELECT t2.Field1 
                     FROM   table t2 
                     WHERE  t2.Field1 = t1.Field1 
                     ORDER  BY Field2, 
                               Field3 DESC 
                     LIMIT  2) 
GROUP  BY field1

Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Alex, Mar 06 '19 at 14:38
@renemarxis Then check the link give in the comment immediately above your comment. Or upgrade to MySQL 8+ and use my answer. — Tim Biegeleisen, Mar 06 '19 at 14:43

Tim Biegeleisen · Accepted Answer · 2019-03-06T14:41:25.243

A nice way to handle this if you are using MySQL 8+ or later is to use ROW_NUMBER:

WITH cte AS (
    SELECT Field1, Field2, Field3,
        ROW_NUMBER() OVER (PARTITION BY Field1, Field2 ORDER BY Field3 DESC) rn
    FROM yourTable
)

SELECT Field1, Field2, Field3
FROM cte
WHERE rn <= 2
ORDER BY Field1, Field2 DESC, Field3;

Demo

If you are not using MySQL 8+, then this problem gets fairly hairy, fairly quickly. The best way to do this in MySQL 5.7 or earlier would be to use session variables to simulate ROW_NUMBER. But, this requires some verbose code. You could also try to isolate the top two entries per group, but that two would take some verbose code. Really, if you have a long term need to run queries like this, then consider upgrading to MySQL 8+.

FWIW, I think the 5.7 query is the same character length as the 8.0 query — Strawberry, Mar 06 '19 at 14:54

Paul Spiegel · Answer 2 · 2019-03-06T15:31:23.163

Pre MySQL 8.0 solution:

List only the last two values per (Field1, Field2) group:

select *
from test t
where t.Field3 >= coalesce((
  select t1.Field3
  from test t1
  where t1.Field1 = t.Field1
    and t1.Field2 = t.Field2
  order by t1.Field3 desc
  limit 1
  offset 1
), 0)
order by Field1, Field2, Field3;

Result:

Field1  Field2  Field3
a       1       3
a       1       4
a       2       2
a       2       3
a       3       1
b       1       2
b       1       3
b       2       1
c       1       1

Get SUMs per Field1 group:

select t.Field1
     , sum(Field2)
     , sum(Field3)
from test t
where t.Field3 >= coalesce((
  select t1.Field3
  from test t1
  where t1.Field1 = t.Field1
    and t1.Field2 = t.Field2
  order by t1.Field3 desc
  limit 1
  offset 1
), 0)
group by t.Field1

Result:

Field1  sum(Field2)     sum(Field3)
a       9               13
b       4               6
c       1               1

Fiddle demo

Note: this works fine, when the combination of (Filed1, Filed2, Filed3) is UNIQUE. If Field3 can be duplicated within a (Field1, Field2) group, we would need more complex logic to limit the result to two rows per group.

mysql limit in group by

2 Answers2

Demo

Pre MySQL 8.0 solution: