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
 
IDUsernameScoreClass
1A1C1
2B2C1
3C3C1
4D1C2
5E2C2
6F3C3
7G5C4
8H6C4

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:
 
IDUsernameScoreClass
1A1C1
2B2C1
4D1C2
5E2C2
6F3C3
7G5C4
8H6C4


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