Three ways to get Top N rows from MySQL

3 ways to get Top N rows from MySQL

There are three different ways to get top N records from a MySQL table.

Below we use the Products table in MySQL Northwind datatbase to demonstrate how to get top 10 most expensive products. The three queries return the same result. It demonstrates how MySQL limits the number of records selected.

Method 1:

The first query uses LIMIT clause to constrain the number of rows returned by the SELECT statement.

select ProductName as Ten_Most_Expensive_Products, 
	UnitPrice    
from Products
order by UnitPrice desc
limit 10;

Method 2:

The second query uses correlated subquery to get the top 10 most expensive products.

select distinct ProductName as Ten_Most_Expensive_Products, 
         UnitPrice
from Products a
where 10 >= (select count(distinct(UnitPrice))
                    from Products b
                    where b.UnitPrice >= a.UnitPrice)
order by UnitPrice desc;

Method 3:

The third query uses ROW_NUMBER function to assign a sequentially incremented row number based on unit price, then select the top 10 records.

select Ten_Most_Expensive_Products, UnitPrice
from
(
    select distinct ProductName as Ten_Most_Expensive_Products, 
           UnitPrice,
           ROW_NUMBER() over (order by UnitPrice desc) as PriceRank
    from Products
    order by UnitPrice desc
) as x
where PriceRank between 1 and 10;

Top 10 most expensive products

Happy Coding!

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Fill Gaps in Sales Data

6. How to Calculate Totals, Subtotals and Grand Total

7. How to Work with NULL Values

8. How to fill down empty cells with values from a previous non-empty row

9. Use RANK function to update a previous record within a group or partition

10. Two ways to add a unique number or ID to each row

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page

	Custom Search