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:
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:
To display the table, execute the command:
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:
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:
By following the above general syntax, we will display the first two rows of the table:
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.
We can see from the above output, the students with st_id 1 and 3 are displayed because both belong to Chicago city.
Conclusion
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.
