MySQL select TOP 10 rows

There is often a requirement to get the top 10 rows from a table, query, or a group in a table. Let us look into each of these.

Table of Contents

• MySQL select TOP 10 rows from a table
  • using LIMIT clause
  • using Row_Number() function
• MySQL select TOP 10 rows from a query result
• MySQL select TOP 10 rows from each group

Let us get started by making the data to be used across. We will be creating a table named students_data and inserting rows to it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_grade VARCHAR(255),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);
INSERT INTO students_data (student_name,student_grade,student_subject) 
VALUES("Gustav","A","Physics"),
("Henric","B","Geography"),
("Richa","C","Physics"),
("Margit","A","Physics"),
("Keshav","A","Geography"),
("James","A","Physics"),
("Joe","A","Physics"),
("Robert","A","Geography"),
("Mary","A","Physics"),
("Jennifer","A","Chemistry"),
("Mark","B","Physics"),
("Paul","B","Geography"),
("Steven","B","Geography"),
("Kevin","B","Chemistry"),
("George","B","Chemistry"),	
("Edward","B","Physics"),
("Betty","B","Physics"),
("Sandra","B","Chemistry"),
("Amy","B","Geography"),
("Helen","C","Geography"),
("Angela","C","Geography"),
("Pamela","C","Chemistry"),
("Anny","C","Chemistry"),	
("Frank","C","Physics"),
("Raymond","C","Geography"),
("Tanya","C","Geography"),
("Victoria","C","Chemistry"),
("Kelly","C","Chemistry"),	
("Lauren","C","Physics"),	
("Rasmus","B","Physics"),
("Erick","C","Geography"),
("Tanya","D","Geography"),
("Monika","A","Chemistry"),
("Atharv","A","Chemistry"),	
("Eva","D","Physics"),
("Joan","D","Physics"),
("Jacob","C","Chemistry"),
("Thomas","D","Geography"),
("Rohit","A","Geography"),
("Henry","A","Geography");

40 rows got inserted into the table. Let us have a view of students_data table by executing SELECT * FROM students_data;

Output:-

MySQL select TOP 10 rows from a table

In this section, we will select the top 10 rows from the table students_data.

Get TOP 10 rows using LIMIT clause

LIMIT clause is used when we need only a specified number of rows from a result set. Using the LIMIT clause optimizes the query as we need not get all the results from the table or query when only a particular number of rows is required. Observe the below query for the solution.

SELECT 
    *
FROM
    students_data
ORDER BY student_id ASC
LIMIT 10;

Action Output Response Message:-

10 row(s) returned.

Output:-

Top 10 rows ordered by student_id are retuned in the output out of 40 rows in the table students_data.

Get TOP 10 rows using ROW_NUMBER() function

MySQL introduced ROW_NUMBER() function in version 8.0. This function assigns a sequential number to each row, beginning with one. 

Observe the below query to view the solution for getting the top 10 rows.

SELECT *
FROM 
    (SELECT student_id, student_name ,
student_grade,
student_subject ,
         ROW_NUMBER()
        OVER (ORDER BY student_name) AS row_num
    FROM students_data) t
WHERE row_num BETWEEN 1 AND 10; 

Action Output Response Message:-

10 row(s) returned.

Output:-

The output in figure 3 shows the top 10 rows from the table students_data ordered by student_name.

In case we want to avoid the row_num column in the output, use the below query.

SELECT student_id, student_name ,
student_grade,
student_subject  FROM (SELECT 
	ROW_NUMBER() OVER (
		ORDER BY student_name
	) row_num,
   student_id, student_name ,
student_grade,
student_subject 
FROM 
	students_data
ORDER BY 
	student_name) as student_data_temp
WHERE row_num <11;

Output:-

MySQL select TOP 10 rows from a query

Let us take a simple example to select the top 10 rows from a query. We will Get the students with Grade ‘A’ from table students_data.

SELECT 
    *
FROM
    students_data
    WHERE student_grade="A"

Action Output Response Message:-

12 row(s) returned.

Output:-

So, we have 12 students with Grade ‘A’. Let us now get the top 10 students with Grade ‘A’. Observe the below query with LIMIT clause.

SELECT 
    *
FROM
    students_data
WHERE
    student_grade = 'A'
LIMIT 10;

Action Output Response Message:-

10 row(s) returned.

MySQL select TOP 10 rows from each group

In this section, along with ROW_NUMBER() function we are also using PARTITION BY. PARTITION BY is a clause to break the entire rows into smaller sections; these partitions are set up by valid expressions formed by GROUP BY / ORDER BY clause.

Let us get the top 10 rows from the student_data table from each group formed by grades. Observe the below query for the solution.

SELECT *
FROM 
    (SELECT student_id, student_name ,
student_grade,
student_subject ,
         ROW_NUMBER()
        OVER (PARTITION BY student_grade ORDER BY student_name ASC) AS row_num
    FROM students_data) temp_data
    WHERE row_num BETWEEN 1 AND 10;  

Action Output Response Message:-

34 row(s) returned.

Output:-

As we can see in output-figure 7, 34 rows are retrieved out of 40 rows from the table students_data. The top 10 rows of each group are retrieved when grouped by student_grade.

Note:- There were only 4 students with grade ‘D’

We hope this article helps you with selecting TOP TEN rows in MySQL. Good Luck !!!