ROW_NUMBER in SQL – Select Top Example in SQL and SQL Server

SQL, or structured query language, lets us gather data from a database through queries. It also enables us to insert, update, and delete that data.

In this blog post, we will focus on how to fetch data and limit the results using SQL.

Why should you limit SQL query results?

A database is usually a huge collection of data. Sometimes we don't need to fetch all of the results. To limit the results, we can optimize the query.

Limiting query results is important for DB performance. Fetching a large result when it is not required incurs extra load on the database and impacts user experience.

How to limit query results in SQL

The syntax is different for SQL Server, Oracle, and MySQL for limiting the data.

• MySQL uses LIMIT.
• ORACLE uses FETCH FIRST .
• MS Access and SQL Server use TOP.

We'll see examples of how each one works in detail below.

Demo Database

We have the following table named students with their details as you can see below:

How to limit a query in MySQL

Below is the syntax for MySQL.

SELECT  (expression)
FROM 
    table_name
LIMIT 5;

As an example, we'll select the first 5 records from the table.

Let's use our table students for this demonstration.

-- fetch top 5 values from table

SELECT * FROM students
LIMIT 5;

Output:

How to Combine LIMIT with ORDER BY

When you combine LIMIT with ORDER BY, you can get more meaningful results. For example we can use this to find the top 5 students who scored greater than 70% on their exam.

Let's order our table students with the column name and choose the top 5 from the result. You can do that like this:

SELECT * FROM students
order by name
LIMIT 5;

Output:

How to limit results – Oracle syntax

Below is the equivalent syntax for our first example in Oracle.

SELECT * FROM students
FETCH FIRST 5 ROWS ONLY;

In older versions of Oracle, you can use ROWNUM to restrict the number of rows returned by a query.

Example:

SELECT * FROM 
students 
WHERE ROWNUM < 5;

How to limit results in SQL – MS Access syntax

Below is the equivalent syntax for our first example in MS Access.

SELECT TOP 5 * FROM students;

Wrapping up

The LIMIT functionality can be very powerful for query optimization when combined with sorting. Efficient queries are lighter on the system and swift for the user. It is always recommended to LIMIT the results where applicable.