Back to list Standard SQL How to Rank Rows Within a Partition in SQL Database: SQL MySQL PostgreSQL Oracle SQL Server SQLite Operators:RANK, PARTITION BY, OVER, ORDER BY Problem: In the result set, you’d like to partition records and rank rows within each partition, adding another column to show rows’ ranks within the partition. Example: Our database has a table named magazine with data in the following columns: id (primary key), name, category, and price. idnamecategoryprice 105Country Livinglifestyle1.70 108News Magazinenews3.35 115Decibelmusic6.50 123Drum Magazinemusic6.50 145Sunsetlifestyle12.00 155Worldlifestyle9.50 158Keyboardmusic8.45 Let’s group magazines according to category and rank each group in descending order by price. Display the ranking position, name, category, and price for each magazine. Solution: SELECT category, name, price, RANK() OVER (PARTITION BY category ORDER BY price DESC ) AS price_rank FROM magazine; Here is the result: categorynamepriceprice_rank lifestyleSunset12.001 lifestyleWorld9.502 lifestyleCountry Living1.703 musicKeyboard8.451 musicDecibel6.502 musicDrum Magazine6.502 newsNews Magazine3.351 Discussion: To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause. SQL’s RANK() function allows us to add a record’s position within the result set or within each partition. In our example, we rank rows within a partition. The OVER() clause always comes after RANK(). OVER() must contain an ORDER BY clause. If you’re returning ranks within a partition, place a PARTITION BY clause inside the OVER() clause. PARTITION BY is followed by an expression or column name; in our example, we use the column category (PARTITION BY category). Note: If you’re not using partitions, you can omit PARTITION BY and simply put the ORDER BY clause in OVER(). After PARTITION BY, place ORDER BY followed by the names of the sorting columns or expressions. In our example, we’re sorting in descending order by the column price (ORDER BY price DESC). The DESC keyword indicates a descending sort order. In the above example, using RANK() with PARTITION BY divided the results set into separate groups of magazines by their category. With each group, rows were sorted by price and then ranked within that group. Recommended courses: SQL Basics SQL Practice Set Window Functions Recommended articles: How to Use Rank Functions in SQL SQL Window Functions By Explanation A Gentle Introduction to Common SQL Window Functions Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Find Duplicate Rows in SQL? How to Find the Number of Days Between Two Dates in MySQL How to Find the Last Day of the Month for a Given Date in MySQL How to Change Seconds to a Time Value in MySQL How to Change Datetime Formats in MySQL How to Get the Date from a Datetime Column in MySQL How to Get the Current Date and Time in MySQL How to Add Days to a Date in MySQL How to Add Time to a Datetime Value in MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.