Mysql query with IN clause and limit for each item

Question

I have a table in MySQL with a field "class_id". I need to write a query which returns top 15 rows sorted using descending time order, for each value in the list with IN clause.

Query for explanation:

select * from table_x where class_id IN (1,2,3) sort by time_x desc limit 15;

In the above example query, I need to fetch top 15 rows for each class_id (1,2 and 3) sorted based on descending time order.

Answer 1

You need the help of MySQL user defined variables

SELECT 
*
FROM 
(
    SELECT
        X.*,
        IF(@sameClass = class_id, @rn := @rn + 1,
             IF(@sameClass := class_id, @rn := 1, @rn := 1)
        ) AS rank
    FROM    table_x AS X
    CROSS JOIN (SELECT @sameClass := 0, @rn := 1 ) AS var
    WHERE   class_id IN (1, 2, 3) 
    ORDER BY class_id, time_x DESC
) AS t
WHERE t.rank <= 15
ORDER BY t.class_id, t.rank

In your case LIMIT 15 actually restricts the result set to contain at most 15 records which is not what you wanted.