What is SELECT TOP?

The TOP clause is used in SQL Server to limit the number of rows returned by a SELECT statement. SQLite does not support the TOP clause. Instead, SQLite uses the LIMIT clause to restrict the number of rows returned.

SQLite Equivalent of SELECT TOP

In SQL Server, you might write SELECT TOP 5. In SQLite, the equivalent is LIMIT 5 at the end of the query.

SELECT name
FROM employees
LIMIT 5;

Selecting the First 5 Records

The SQLite LIMIT clause can be used to return only the first five records from a table.

SELECT name, salary
FROM employees
LIMIT 5;

LIMIT with ORDER BY

To retrieve the highest or lowest values, combine ORDER BY with LIMIT. This is the SQLite equivalent of using TOP with ORDER BY in SQL Server.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

LIMIT with WHERE

You can combine WHERE, ORDER BY, and LIMIT to filter records and then return only a specific number of matching rows.

SELECT name, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC
LIMIT 10;

LIMIT with Multiple Columns

You can select multiple columns and use LIMIT to restrict the number of rows returned.

SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

LIMIT with DISTINCT

SQLite also allows DISTINCT to be combined with LIMIT to return a limited number of unique values.

SELECT DISTINCT department
FROM employees
LIMIT 5;

LIMIT with OFFSET

SQLite supports OFFSET with LIMIT. This allows you to skip a specified number of rows before returning the requested number of records.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

Finding the Highest-Paid Employees

By sorting salary in descending order and using LIMIT, you can retrieve the employees with the highest salaries.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Finding the Lowest-Paid Employees

To find the employees with the lowest salaries, sort the salary column in ascending order and use LIMIT.

SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 3;

SQL Server TOP vs SQLite LIMIT

SQL Server and SQLite use different syntax for limiting query results. SQL Server uses TOP, while SQLite uses LIMIT.

Why Use LIMIT in SQLite?

• Restricts the number of rows returned.
• Works directly with SQLite.
• Can be combined with WHERE and ORDER BY.
• Supports pagination using OFFSET.
• Useful when working with large result sets.

Best Practices

• Use ORDER BY when you need predictable results.
• Use WHERE to filter records before applying LIMIT.
• Use LIMIT instead of TOP when working with SQLite.
• Use OFFSET when implementing pagination.
• Use DISTINCT with LIMIT when you need unique values.