MySQL Group_concat gets the first few groups

If it is Oracle it should be easy to implement with partition by.

For example, to get the top 3 class, you can use Group_concat + GROUP by + substring_index implementation.

Test form

DROP TABLE IF EXISTS ' test ';
CREATE TABLE ' Test ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar (DEFAULT NULL),
' Score ' int (one) DEFAULT NULL,
' Class ' char (+) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;

Inserting data

INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 1 ', ' bobdd ', ' 25 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 2 ', ' xx ', ' 20 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 3 ', ' Jack ', ' 30 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 4 ', ' Bill ', ' 32 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 5 ', ' Nick ', ' 22 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 6 ', ' Kathy ', ' 18 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 7 ', ' Steve ', ' 36 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 8 ', ' Anne ', ' 25 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 9 ', ' Kathy ', ' 18 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' One ', ' Bob1 ', ' 25 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Jane1 ', ' 20 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Jack1 ', ' 30 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Bill1 ', ' 32 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' A ', ' Nick1 ', ' 22 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Kathy1 ', ' 18 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Steve1 ', ' 36 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Anne1 ', ' 25 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Kathy1 ', ' 18 ', ' 2 ');

Use Group_concat + GROUP by group to get top 3

Select Group_concat (t1.id) as IDs from (
SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3) as-ID from
Test T GROUP by T.class
) T1

Get

Note that t.id ORDER by t.score DESC scores from high to low.

The above statement simply gets the total ID. But the conversion to the column is not very good to get. You can split it up with union all.

Get first place

Select Group_concat (t1.id) as IDs from (
SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3) as-ID from
Test T GROUP by T.class
) T1

UNION ALL

--second place
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 2), ', ', -1) as-ID from
Test T GROUP by T.class

UNION ALL

--Third place
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3), ', ', -1) as-ID from
Test T GROUP by T.class

Well, now that we've got a list,

Use in to complete the final step

SELECT class,score,name from test where ID in (
SELECT ID from
(SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 1) as-ID from
Test T GROUP by T.class
UNION ALL
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 2), ', ', -1) as-ID from
Test T GROUP by T.class
UNION ALL
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3), ', ', -1) as-ID from
Test T GROUP by T.class) T2
) ORDER by Class Asc,score desc

Final result

MySQL Group_concat gets the first few groups