Select first N rows in group with MySQL

Ngày 16 tháng 11 năm 2012 Trương Chương Dương

The question is:
1. I has table mark

ID	Username	Score	Class
1	A	1	C1
2	B	2	C1
3	C	3	C1
4	D	1	C2
5	E	2	C2
6	F	3	C3
7	G	5	C4
8	H	6	C4

2. I would like to get top 2 user each class which higest score. It mean I get the top 2 user each "group by class".
3. The result should be:

ID	Username	Score	Class
1	A	1	C1
2	B	2	C1
4	D	1	C2
5	E	2	C2
6	F	3	C3
7	G	5	C4
8	H	6	C4

Solution:

SELECT
    `m`.*
FROM
    `mark` `m`,
    (
    SELECT
        GROUP_CONCAT(`id` ORDER BY `score` DESC) `ids`,
        `tmp`.`class`
    FROM
        (
        SELECT
            *
        FROM
            `mark`
        WHERE `username` LIKE 'test 1%' -- Any condition you want to search will be here
        ) AS `tmp`
    GROUP BY `tmp`.`class`
    ) `tmp`
WHERE
    `m`.`username` LIKE 'test 1%' -- Any condition you want to search will be here again
    AND `m`.`class` = `tmp`.`class`
    AND FIND_IN_SET(`m`.`id`, `tmp`.`ids`) <= 2 -- Change this value for maximium of the rows you want to get
-- ORDER BY FIND_IN_SET(`m`.`id`, `tmp`.`ids`) DESC -- Add order if you want

Đang tải dữ liệu...

Chuong Duong's blogs

Select first N rows in group with MySQL

Giới thiệu về website của chúng tôi!