SQL LIMIT/TOP Keyword

Read this article to understand the SQL LIMIT TOP Keyword/clause along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of LIMIT clause with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of LIMIT or TOP clause and why we should use it.

Definition

The LIMIT Clause or Keyword is used to limit the number of rows which are returned by SQL query. Sometimes we want to print only top few records. Sometimes we need to filter the data based on top records. Some database doesn’t support LIMIT while some doesn’t support TOP keyword. Use of both keywords are same.

Reasons to Use

As it’s name suggest we can limit the number of records which are returned by the select query. Below are the some key areas where we need to use LIMIT clause.

1. Show the top 10 news channels from INDIA country.
2. Show the top 5 products which has price range between 100 to 1000.
3. Show the top rated applications from the market place.
4. Show the top 3 highest salaried employees.

Syntax

SELECT column1, column2, columnN 
FROM table_name
LIMIT number;

SELECT column1, column2, columnN  
FROM table_name 
LIMIT number1 OFFSET number2;

SELECT column1, column2, columnN  
FROM table_name 
[WHERE condition]
LIMIT number1 OFFSET number2;

SELECT TOP number 
column1, column2, columnN   
FROM table_name 
[WHERE condition];

Above are the syntax of LIMIT clause as well as syntax of TOP clause. TOP clause is used with Microsoft SQL only while the limit clause supports to PostgreSQL, MySQL, Oracle etc.

Example

Example 1:

Let’s show top 3 highest age customers from Customers table.

customer_id	first_name	last_name	age	country
3	David	Robinson	32	UK
1	John	Doe	31	USA
2	Robert	Luna	22	USA

Example 2:

Let’s show top 3 highest age customers from Customers table by excluding first 2 customers.

customer_id	first_name	last_name	age	country
2	Robert	Luna	22	USA
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

I hope all above example clears the concept of LIMIT clause and it’s use.

Linking

LIMIT clause is used in Select statement. We can use Where clause with LIMIT clause. Order By clause is also used with LIMIT clause. You can refer all the interview questions. Please refer all the links for understanding the LIMIT or TOP in depth.

SQL LIMIT/TOP Keyword with Latest SQL Interview Questions 2023

Basic LIMIT/TOP SQL Interview questions

1. What is the syntax for using the LIMIT clause in SQL?
2. How does the TOP clause in SQL differ from the LIMIT clause?
3. Can you use the LIMIT clause with OFFSET to implement pagination?
4. Is the LIMIT clause supported by all SQL databases?

Intermediate LIMIT/TOP SQL Interview questions

1. How do you retrieve the first 5 rows after the first 10 rows from a table using LIMIT and OFFSET?
2. Can you use the LIMIT clause with an ORDER BY clause?

Advanced LIMIT/TOP SQL Interview questions

1. How do you retrieve the top 3 highest values from a column using the TOP clause in Microsoft SQL Server?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : How do you retrieve the first 5 rows after the first 10 rows from a table using LIMIT and OFFSET?

Ans : Check the below syntax for getting first 5 rows after the first 10 rows skipping.

SELECT * FROM Customers
LIMIT 5 OFFSET 10;

Quiz

Click on this link to solve few MCQ questions on LIMIT clause.

I hope you have enjoyed this session. Happy offline learning… Lets meet in next session.