Mixing
Using LIMIT within GROUP BY to get N results per dynamic group
up vote
0
down vote
favorite
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:
Select the top N rows from each group Not giving true result it's broken.- Only work in static columns.
php mysql database subquery dynamic-queries
edited 2 days ago
Barry
3,11571637
asked 2 days ago
Siberhecy
2119
add a comment |
up vote
0
down vote
favorite
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:
Select the top N rows from each group Not giving true result it's broken.- Only work in static columns.
php mysql database subquery dynamic-queries
edited 2 days ago
Barry
3,11571637
asked 2 days ago
Siberhecy
2119
1
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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:
Select the top N rows from each group Not giving true result it's broken.- Only work in static columns.
php mysql database subquery dynamic-queries
edited 2 days ago
Barry
3,11571637
asked 2 days ago
Siberhecy
2119
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:
Select the top N rows from each group Not giving true result it's broken.- Only work in static columns.
php mysql database subquery dynamic-queries
php mysql database subquery dynamic-queries
edited 2 days ago
Barry
3,11571637
asked 2 days ago
Siberhecy
2119
edited 2 days ago
Barry
3,11571637
asked 2 days ago
Siberhecy
2119
edited 2 days ago
Barry
3,11571637
edited 2 days ago
Barry
3,11571637
edited 2 days ago
Barry
3,11571637
3,11571637
asked 2 days ago
Siberhecy
2119
asked 2 days ago
Siberhecy
2119
asked 2 days ago
Siberhecy
2119
2119
1
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago
add a comment |
1
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago
1
1
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
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 |
+----+--------+------------+-------+
answered 2 days ago
Strawberry
25.7k83149
You are a hero, thank you!
– Siberhecy
2 days ago
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
2 days ago
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
add a comment |
up vote
-1
down vote
You can also do workaround.
Select colums upto 2 rows
FROM TABLE ORDER BY DESCENDING GROUP LEVEL
regards,
Umar Abdullah
answered 2 days ago
Umar Abdullah
1
This query order everything by level not order by each group.
– Siberhecy
2 days ago
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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 |
+----+--------+------------+-------+
answered 2 days ago
Strawberry
25.7k83149
You are a hero, thank you!
– Siberhecy
2 days ago
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
2 days ago
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
add a comment |
up vote
2
down vote
accepted
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 |
+----+--------+------------+-------+
answered 2 days ago
Strawberry
25.7k83149
You are a hero, thank you!
– Siberhecy
2 days ago
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
2 days ago
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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 |
+----+--------+------------+-------+
answered 2 days ago
Strawberry
25.7k83149
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 |
+----+--------+------------+-------+
answered 2 days ago
Strawberry
25.7k83149
answered 2 days ago
Strawberry
25.7k83149
answered 2 days ago
Strawberry
25.7k83149
answered 2 days ago
Strawberry
25.7k83149
25.7k83149
You are a hero, thank you!
– Siberhecy
2 days ago
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
2 days ago
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
add a comment |
You are a hero, thank you!
– Siberhecy
2 days ago
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
2 days ago
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
You are a hero, thank you!
– Siberhecy
2 days ago
You are a hero, thank you!
– Siberhecy
2 days ago
1
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
2 days ago
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
2 days ago
1
1
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@TimBiegeleisen To be fair, at some point in the future we'll be using telepathy for these kinds of problems.
– Strawberry
2 days ago
@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
2 days ago
@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
2 days ago
@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
2 days ago
@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
2 days ago
add a comment |
up vote
-1
down vote
You can also do workaround.
Select colums upto 2 rows
FROM TABLE ORDER BY DESCENDING GROUP LEVEL
regards,
Umar Abdullah
answered 2 days ago
Umar Abdullah
1
This query order everything by level not order by each group.
– Siberhecy
2 days ago
add a comment |
up vote
-1
down vote
You can also do workaround.
Select colums upto 2 rows
FROM TABLE ORDER BY DESCENDING GROUP LEVEL
regards,
Umar Abdullah
answered 2 days ago
Umar Abdullah
1
This query order everything by level not order by each group.
– Siberhecy
2 days ago
add a comment |
up vote
-1
down vote
up vote
-1
down vote
You can also do workaround.
Select colums upto 2 rows
FROM TABLE ORDER BY DESCENDING GROUP LEVEL
regards,
Umar Abdullah
answered 2 days ago
Umar Abdullah
1
You can also do workaround.
Select colums upto 2 rows
FROM TABLE ORDER BY DESCENDING GROUP LEVEL
regards,
Umar Abdullah
answered 2 days ago
Umar Abdullah
1
edited 2 days ago
answered 2 days ago
Umar Abdullah
1
answered 2 days ago
Umar Abdullah
1
answered 2 days ago
Umar Abdullah
1
1
This query order everything by level not order by each group.
– Siberhecy
2 days ago
add a comment |
This query order everything by level not order by each group.
– Siberhecy
2 days ago
This query order everything by level not order by each group.
– Siberhecy
2 days ago
This query order everything by level not order by each group.
– Siberhecy
2 days ago
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373326%2fusing-limit-within-group-by-to-get-n-results-per-dynamic-group%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What version of MySQL are you using? Why doesn't your current query meet your expectations?
– Tim Biegeleisen
2 days ago
@TimBiegeleisen MySQL version: 5.6.33 Also Strawberry just solved my problem! Yes!
– Siberhecy
2 days ago
Possible duplicate of Get top n records for each group of grouped results
– Nick
2 days ago