0

Using LIMIT within GROUP BY to get N results per dynamic group

Hello everyone, firstly I read about questions like this problem. But didn't get the solution. All of this SQL's are designed for static columns. But I have dynamic columns.

Table:

id  Name      Group Level 
2   Jonathan  A     5 
5   David     A     10
6   Alex      C     10
7   Kemal     A     71
8   John      D     21
9   Celin     F     100
12  Alexis    G     15
13  Noone     A     23

I want to get the first 2 highest Level from each group.

But query must be dynamic because there will be more Groups, which is where I am stuck.

Solutions I tried:

  1. Select the top N rows from each group Not giving true result it's broken.
  2. Only work in static columns.
Barry
  • 3,303
  • 7
  • 23
  • 42
Siberhecy
  • 561
  • 1
  • 5
  • 16
  • 1
    What version of MySQL are you using? Why doesn't your current query meet your expectations? – Tim Biegeleisen Nov 19 '18 at 11:10
  • @TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes! – Siberhecy Nov 19 '18 at 11:36
  • Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Nick Nov 19 '18 at 11:38

2 Answers2

2
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL
,group_name CHAR(1) NOT NULL
,level INT NOT NULL
);

INSERT INTO my_table VALUES
( 2,'Jonathan','A',5),
( 5,'David','A',10),
( 6,'Alex','C',10),
( 7,'Kemal','A',71),
( 8,'John','D',21),
( 9,'Celin','F',100),
(12,'Alexis','G',15),
(13,'Noone','A',23);

SELECT id
     , name
     , group_name
     , level 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = group_name THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=group_name 
         FROM my_table x -- technically, ordering should really happen here, in a separate subquery
            , ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY group_name
            , level DESC
            , id
     ) a 
 WHERE i <=2;
+----+--------+------------+-------+
| id | name   | group_name | level |
+----+--------+------------+-------+
|  7 | Kemal  | A          |    71 |
| 13 | Noone  | A          |    23 |
|  6 | Alex   | C          |    10 |
|  8 | John   | D          |    21 |
|  9 | Celin  | F          |   100 |
| 12 | Alexis | G          |    15 |
+----+--------+------------+-------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • 1
    There is a much better way of doing this in MySQL 8+, and the above approach won't be applicable at some point in the future. – Tim Biegeleisen Nov 19 '18 at 11:37
  • 1
    @TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems. – Strawberry Nov 19 '18 at 11:47
  • @Strawberry Let's think there's one more column as "Suit_Type". Some rows have Suit_A, some rows have Suit_B. But I wanna count them as one in this query. Like GROUP By Suit_Type. I mean **"Kemal - A - 71 - Suit_A"** - **"Kemal - A - 70 - Suit_B"** - **"Noone - A - 69 - Suit_A"**. When query work I don't wanna select both because they're **name is same**. Only Suit's diffrent but It's ok. I just wanna select first. I want this result-> **"Kemal - A - 71 - Suit_A"** and **"Noone - A - 69 - Suit_A"**. Where should I write this? Thank you so much. – Siberhecy Nov 19 '18 at 12:34
  • @Siberhecy The comments section is not the appropriate place for this kind of discussion. If you have a new question, ask a new question (referencing this one, if appropriate) – Strawberry Nov 19 '18 at 13:17
-1

You can also do workaround.

Select colums upto 2 rows

FROM TABLE ORDER BY DESCENDING GROUP LEVEL

regards,

Umar Abdullah

Umar Abdullah
  • 1,282
  • 1
  • 19
  • 37