How to use SQL SELECT TOP

SQL SELECT TOP reduces your data analysis to a certain number of records. It limits the number of records returned, which optimises the performance of your queries and saves resources when working with complex databases.

What is SQL SELECT TOP?

There are a number of tools available to you when you’re working with SQL, such as SQL commands, SQL operators and SQL functions. You can use them to search, analyse, change and manipulate records. Especially when it comes to very large datasets with thousands of entries, it can be very helpful to limit the number of query results. That’s what SQL SELECT TOP is for.

The function is a combination of SQL SELECT and the keyword TOP and allows you to specify the number of results you want to return. That way you can reduce results to the most relevant entries and make them as readable and informative as possible.

Note that SQL SELECT TOP does not work in every database. The commands LIMIT, FETCH FIRST and ROWNUM serve a similar purpose, which we will discuss at the end of this tutorial.

What is the syntax of SQL SELECT TOP?

The basic syntax of SELECT TOP looks as follows:

SELECT  TOP  n ColumnA, ColumnB, …
FROM  Table

It uses the following parameters:

• SELECT TOP: Specifies the number of results that you want to return, as well as which columns you want to retrieve records from. First enter the number of desired results (n), then enter the column name or an asterisk *. The asterisk stands for all records in the table.
• FROM: Specifies the table you want to work in.

You can also use these optional clauses and operators:

• WHERE: An SQL WHERE clause can be used to specify criteria that records must fulfill. It allows you to exclude irrelevant results.
• ORDER BY: The SQL ORDER BY command can be used to sort results in ascending or descending order.

What is SQL SELECT TOP used for?

SQL SELECT TOP is useful for comparing and analysing data and filtering data based on size and quantity. Some practical use cases for the function include:

• Retrieve a selection of customers with the highest order numbers
• Filter the top 10 teams with the most successful marketing campaigns
• Look at the 50 largest transactions to check for suspicious financial activity
• Display the 10 websites with the most traffic in a given month
• Analyse promising customers using demographic, geographic and financial data
• Check inventory by retrieving the 10 products with the lowest stock levels
• Evaluate purchase behaviour using the most frequently ordered or viewed products
• Determine risk factors for patients using relevant test results, symptoms and pre-existing conditions
• Filter suppliers with the shortest delivery times

Examples of SQL SELECT TOP

Below we’ll look at two examples that show how SQL SELECT TOP works in practice.

Showing the 10 most recent orders

Let’s say you want to display the 10 most recent orders from a table called ‘Orders’. The syntax for that would look as follows:

SELECT  TOP  10  *
FROM  Orders
ORDER BY  Order_Date DESC

You can add an ORDER BY clause to sort your results by parameters such as order quantity, price or date.

Customers with the most orders

In this example, you want to retrieve the 10 customers with the most orders from the table ‘Orders’:

SELECT TOP 10 Name, CustomerID, COUNT(*) AS OrderQuantity
FROM Orders
GROUP BY Name, CustomerID
ORDER BY OrderQuantity DESC

You can use the COUNT function to add up all the orders made with a name and customer ID and determine how many orders each customer has made.

Are there alternatives to SQL SELECT TOP?

SQL SELECT TOP isn’t available in every database system. If that’s the case, you might need to use an alternative statement. Here’s a brief overview of which database management systems (DMS) support SELECT TOP and its alternatives: