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;
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.