5

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.

1000111
  • 13,169
  • 2
  • 28
  • 37
Sudheer
  • 327
  • 3
  • 15

1 Answers1

4

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.

1000111
  • 13,169
  • 2
  • 28
  • 37
  • Exactly the one which I am looking for. Teste and it's working fine. Thank you. And, Yes I posted the above query in my question just for better explanation. – Sudheer Sep 18 '16 at 05:05
  • You are welcome. Also note that you easily tune the number of records per class through this line `WHERE t.rank <= n`. You can put any value to n – 1000111 Sep 18 '16 at 05:07