SQL SELECT TOP Clause

The SQL SELECT TOP clause is used to limit the number of rows returned in a query.

It is commonly used in conjunction with the ORDER BY clause to return the top N rows from a table based on a specific sorting criteria.

In this guide, we will go over the syntax, usage, and examples of the SELECT TOP clause in SQL.

Syntax

The syntax for the SELECT TOP clause is as follows:

SELECT TOP number column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
  • number is the number of rows that you want to return.
  • column1, column2, ... are the columns that you want to select. You can use the * wildcard to select all columns.
  • table_name is the name of the table that you want to select data from.
  • ORDER BY column1, column2, ... specifies the sorting criteria for the query. The rows will be returned in ascending order by default, but you can use the ASC or DESC keywords to specify ascending or descending order, respectively.

Usage

The SELECT TOP clause is typically used in combination with the ORDER BY clause to return the top N rows from a table based on a specific sorting criteria.

For example, you can use the following query to return the top 10 customers with the highest total orders:

SELECT TOP 10 Customers.CustomerName, SUM(Orders.OrderAmount) AS TotalOrders
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerName
ORDER BY TotalOrders DESC;

This query returns the top 10 customers with the highest total orders, sorted in descending order by the TotalOrders column.

Examples

Selecting the top 5 rows from a table:

SELECT TOP 5 * FROM Customers;

Selecting top 10 employees with highest salary:

SELECT TOP 10 * FROM Employee
ORDER BY Salary DESC;

Selecting top 5 products with highest quantity in stock:

SELECT TOP 5 ProductName, Quantity FROM Products
ORDER BY Quantity DESC;

SQL TOP LIMIT

The LIMIT clause is used in place of the TOP clause, and the number of rows to return is specified after the keyword LIMIT.

SELECT *
FROM Customers
LIMIT 10;

In this example, the query is selecting all the columns from the "Customers" table and limiting the number of rows returned to 10.

Please note that SQL standard doesn't include the LIMIT clause, it's used by some databases like MySQL, PostgreSQL, SQLite, MariaDB and others.

You can also use the LIMIT clause in combination with the OFFSET clause to skip a certain number of rows before starting to return the results.

SELECT *
FROM Customers
LIMIT 10 OFFSET 20;

This query will return 10 rows starting from the 21st row in the table "Customers".

SQL SELECT TOP PERCENT

The TOP PERCENT clause is used instead of the TOP clause, and the number is specified as a percentage instead of an absolute value.

SELECT TOP 20 PERCENT *
FROM Orders
ORDER BY OrderDate DESC;

In this example, the query is selecting the top 20 percent of rows from the "Orders" table, sorted in descending order by the "OrderDate" column.

Please note that some databases like MSSQL has TOP with percent and others like MySQL does not support it. You need to use different techniques to achieve the similar results.

Conclusion

In this guide, we have covered the basics of the SQL SELECT TOP clause, including its syntax, usage, and examples.

The SELECT TOP clause is a powerful tool that allows you to limit the number of rows returned in a query, making it an essential part of any SQL developer's toolkit.

Remember that SELECT TOP clause can be used with other SELECT statement clauses like WHERE, GROUP BY and HAVING clause to filter or group the records before fetching the top records.

SQL Basics