CODEWITHSUNDEEP
mysql
user1123530
user1123530

Reputation: 561

Getting the top N results of every category in a table

I'd like to extract the top 10 results of a certain category within a table, ordered by date. My table looks like 

CREATE TABLE IF NOT EXISTS Table
( name VARCHAR(50)
, category VARCHAR(50)
, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

So far I've come up with SELECT category FROM Table GROUP BY category;, this will give me every category in store.

Next I need to run SELECT * FROM Table WHERE category=$categ ORDER BY date DESC LIMIT 10; in some kind of foreach loop for every $categ fed to me by the first instruction.

I'd like to do all of this in MySQL, if possible; I've come across several answers online but they all seem to involve two or more tables, or provide difficult examples that seem hard to understand... It would seem silly to me that something that can be dealt with so simply in server code (doesn't even create that much overhead, apart from the needless storage of the category names) is so difficult to translate into SQL code, but if nothing works that's what I'll end up doing, I guess.

Upvotes: 0

Views: 50

Answers (1)

spencer7593
spencer7593

Reputation: 108510

You can use an inline view and user-defined variables to set a "row number" column, and then the outer query can filter based on the "row number" column. (Doing this, we can emulate a ROW_NUMBER analytic function.)

For large sets, this may not be the most efficient approach, but it works reasonably for small sets.

The outer query would look something like this:

SELECT q.*
  FROM ( 
         <view_query>
       ) q
 WHERE q.row_num <= 10 
 ORDER
    BY q.category, q.date DESC, q.name

The view query would be something like this

         SELECT IF(@cat = t.category,@i := @i + 1, @i := 1) AS row_num
              , @cat := t.category AS category
              , t.date
              . t.name
           FROM mytable t
          CROSS
           JOIN ( SELECT @i := 0, @cat := NULL ) i 
          ORDER BY t.category, t.date DESC

Upvotes: 1

Related Questions