CODEWITHSUNDEEP
mysqlsql-server
Ali
Ali

Reputation: 3635

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

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

Upvotes: 0

Views: 315

Answers (1)

Gaspa79
Gaspa79

Reputation: 5615

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

Upvotes: 1

Related Questions