How to use SELECT TOP statement in MySQL

The fast and reliable architecture of MySQL enhances its popularity among its competitors such as PostgreSQL. MySQL is a database management system that is used in the back-end of the website to store and manage the data of the website.

MySQL is an RDMS that uses SQL as a query language, for this reason, most functions and clauses used in SQL can be executed in MySQL but it is not compulsory that every SQL query can be used in MySQL. The same is the case with a SELECT TOP clause which is used in SQL to display the top rows or to perform different operations on them and is not supported by MySQL. In this write-up, we will discuss what the SELECT TOP clause is? What happens if we use the SELECT TOP clause in MySQL? And What is the alternative of SELECT TOP in MySQL?

What is the SELECT TOP clause

In the MySQL database, data is being stored in a combination of rows and columns, these combinations form the shape of tables. A table may consist of thousands of rows now if a user wants to extract the data from specific top rows, the “SELECT TOP” clause can be used for this purpose.

What happens if the SELECT TOP clause is used in MySQL

To understand the working of the “SELECT TOP” clause in MySQL, let us consider a table of school data that has ten rows containing students data which is as follow:

St_id St_Name St_Grade St_City
1 Paul V Chicago
2 Alex I Houston
3 Roger VI Chicago
4 Emma VI Chicago
5 Sophia Sophia New York
6 John III Phoenix

To create this table with name students data, execute the command:

CREATE TABLE students_data (St_id INT, St_Name

VARCHAR (50), St_Grade VARCHAR(10), St_City VARCHAR(50));

To display confirm the creation of table, run the command:

The table has been created, next step is to insert the data in the data, we can do so by using the command:

INSERT INTO students_data VALUES


’Emma’,’VI’,’Chicago’),(5,’Sophia’,’II’,’New York’),(6,’John’,’III’,’Phoenix’);

To display the table, execute the command:

SELECT * FROM students_data;

In the above table, if we want to display only two rows by using the SELECT TOP clause a error should be generated after executing the command:

SELECT TOP 2 * FROM students_data;

It generated the syntax error which means this clause is not working in MySQL, but there is another way to obtain the results and that is by using the LIMIT clause.

How to use the LIMIT clause instead of the SELECT TOP clause in MySQL

The LIMIT clause can be used to display the limited number of rows of the table in MySQL. The general syntax of using the LIMIT clause is:

SELECT * FROM table_name LIMIT [rows-number-to-be-displayed]

By following the above general syntax, we will display the first two rows of the table:

SELECT * FROM students_data LIMIT 2;

We can also use the LIMIT clause with the WHERE clause, to understand this we will again consider the above table and execute the command to display the first two rows of students_data who belong to the city, Chicago.

SELECT * FROM students_data  WHERE St_City = ‘Chicago’  LIMIT 2;

We can see from the above output, the students with st_id 1 and 3 are displayed because both belong to Chicago city.


The clauses are used in databases to provide ease in the programming skills to the developers so that they can obtain their results by executing these clauses. The syntax of MySQL and SQL are much similar but there are many clauses that work in SQL but do not work in MySQL like the SELECT TOP clause. In this write-up, we have discussed the SELECT TOP clause and the syntax error generated while running the clause. Then we discussed the LIMIT clause with the help of which we can obtain the same results of the SELECT TOP clause.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.