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...
web site traffic statistics