Convert sql query to mySql,Need Top 5 records of each category

Question

I need top 5 records of each category. Below query executed fineon sqlserver but not my sql,Need to convert into mySql

SELECT N.TITLE AS NewsTitle , N.ID AS NewsID, NC.NAME AS NewsCategory, NC.ID AS NewsCatID
    FROM (
        SELECT N.TITLE AS NewsTitle , N.ID AS NewsID, NC.NAME AS NewsCategory, NC.ID AS NewsCatID, Rank() over (Partition BY NC.[NAME] ORDER BY N.TITLE ) AS Rank
        FROM News N
        INNER JOIN News_Category NC ON NC.ID = N.ID_News_Category
        ) rs WHERE Rank <= 5

just add: LIMIT 5; at the end of each query and you should be fine, don't forget to test if it gives you the expected result. — , Feb 13 '12 at 11:27
You might need an "order by Rank desc limit 5" to get the highest ones listed. — FreudianSlip, Feb 13 '12 at 11:53
@DorinDuminica: It's not a simple "just add `LIMIT 5`". The OP wants 5 top of each category, not just the top 5 of all the table. — ypercubeᵀᴹ, Feb 13 '12 at 15:09

score 1 · Answer 1 · edited May 23 '17 at 12:11

There is no RANK function is MySQL. You can use a similar approach here:

Rank function in MySQL

However, the problem is that this won't take on account "ties" in ranks. Your 2 comments stating that you just have to add Limit 5 are almost 100% accurate.

You can (and probably will) add some Limit 5 along with an order by clause, and this will almost completely solve your problem. The only thing that would be left is that Rank allows ties. Meaning that when you select where the Rank <=5, you can have 6 records or more as a result (due to them being "tied" in rank, check here for more details: http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html).

So in conclusion, you should think if you really are that worried about ties. If you are, you should google if someone actually made some kind of rank function for mysql similar to t-sql's rank function. The second answer of the stackoverflow link has a kind of rank column that allows ties, you should check it. If you aren't that worried, just add some LIMIT 5 in your query as the top commenters say and you'll be fine.

EDIT: I found an EXCELLENT site that has a way of doing rank in MySQL! http://www.artfulsoftware.com/infotree/queries.php?&bw=1024#460

Convert sql query to mySql,Need Top 5 records of each category

1 Answers1