How to get Top 10 or N Rows in MySQL

· 578 words · 3 minute read

How to Get the Top 10 Rows or Top N rows from a Table in MySQL 🔗

What You’ll Learn 🔗

In this tutorial, you’ll discover how to use the LIMIT clause and the ORDER BY clause to retrieve the top 10 rows from a table in MySQL. You’ll learn how to write efficient queries, optimize performance, and master best practices for using these essential SQL clauses.

The LIMIT Clause: Retrieving a Specific Number of Rows 🔗

The LIMIT clause is used to specify the number of rows to return from a query. It’s essential for managing large datasets, improving performance, and reducing the load on your server.

Syntax and Examples 🔗

The basic syntax of the LIMIT clause is:

SELECT column1, column2, ...
FROM tablename
LIMIT number_of_rows;

For example, to retrieve the top 10 rows from a employees table, you can use the following query:

SELECT employee_name, salary
FROM employees
LIMIT 10;

The ORDER BY Clause: Sorting Your Data 🔗

You can also use the ORDER BY clause is used to sort your data in a specific order. When used with the LIMIT clause, it ensures that you retrieve the top rows based on an ordering.

Syntax and Examples 🔗

The basic syntax of the ORDER BY clause is:

SELECT column1, column2, ...
FROM tablename
ORDER BY column_name ASC/DESC;

For example, to retrieve the top 10 employees with the highest salary, you can use the following query:

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

Using OFFSET with LIMIT 🔗

In addition to specifying the number of rows to retrieve, you can also use the OFFSET keyword to retrieve a specific range of rows.

Syntax and Examples 🔗

The syntax for using OFFSET with LIMIT is:

SELECT column1, column2, ...
FROM tablename
LIMIT offset, number_of_rows;

For example, to retrieve the top 10 employees with the highest salary, starting from the 5th row, you can use the following query:

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 4, 10;

Exercises and Solutions 🔗

Exercise 1: Retrieving the Top 5 Countries with the Highest Population 🔗

Retrieve the top 5 countries with the highest population from a countries table.

Country Population Capital Area (sq km) Official Language
China 1,412,000,000 Beijing 9,596,961 Chinese
India 1,366,000,000 New Delhi 3,287,263 Hindi, English
United States 331,000,000 Washington, D.C. 9,826,675 English
Indonesia 273,000,000 Jakarta 1,904,569 Indonesian
Pakistan 220,000,000 Islamabad 881,913 Urdu, English
Brazil 213,000,000 Brasília 8,515,767 Portuguese
Nigeria 206,000,000 Abuja 923,768 English
Bangladesh 166,000,000 Dhaka 147,570 Bengali
Russia 146,000,000 Moscow 17,098,242 Russian
Mexico 128,000,000 Mexico City 1,964,375 Spanish

Solution

SELECT country_name, population
FROM countries
ORDER BY population DESC
LIMIT 5;

Exercise 2: Retrieving the Top 3 Products with the Lowest Price 🔗

Retrieve the top 3 products with the lowest price from a products table.

Product Name Price Category In Stock Rating
Apple iPhone 13 $799 Electronics Yes 4.8
Samsung Galaxy S21 $749 Electronics Yes 4.7
Sony WH-1000XM4 $349 Electronics Yes 4.6
Dell XPS 13 $999 Computers No 4.5
Apple MacBook Air $999 Computers Yes 4.8
Nike Air Max 270 $150 Footwear Yes 4.7
Adidas Ultraboost $180 Footwear Yes 4.6
Instant Pot Duo $89 Home Appliances No 4.7
Sony PlayStation 5 $499 Gaming No 4.9
Nintendo Switch $299 Gaming Yes 4.8

Solution

SELECT product_name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Best Practices 🔗

When using the LIMIT and ORDER BY clauses, remember to:

  • Use indexes on the columns used in the ORDER BY clause to improve performance.
  • Avoid using SELECT * and instead specify the columns you need.
MySQL