|
Custom Search
| |
|
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;
Happy Coding!
Other tutorials in this category 1. MySQL Northwind Queries - Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |