MySQL group the first few entries

• Using GROUP_CONCAT and SUBSTRING_INDEX implementation, can make good use of index, suitable for big data.

SELECT 
    *
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ', ',
                        1) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC;
Copy the code

• If multiple entries are selected, SUBSTRING_INDEX(x,x,8) uses the third parameter 3 to limit the number of entries to be grouped. The outer layer returns all primary keys via GROUP_CONCAT.

Since IN (GROUP_CONCAT()) abandons index use IN subqueries as follows:

SELECT * from yourtable where id in(
SELECT 
    GROUP_CONCAT(ID)
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ', ',
                        8) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC
) 
Copy the code

So please choose whether to use 2 statements to query according to EXPLAIN plan.

If you still want a statement, fine.

SELECT * from yourtable where FIND_IN_SET(id,(
SELECT 
    GROUP_CONCAT(ID) id
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY column_2 DESC),
                        ', ',
                        8) id
        FROM
            yourtable
        GROUP BY column_1)
ORDER BY column_2 DESC
))
Copy the code