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:-
Frequently Asked:

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 !!!