The SQL Server TOP clause is used to limit the number of rows that are returned by a query. Usually, Databases and tables hold millions or billions of records. So, if you want to see the top 10 products, we can use the TOP Clause and extract the required number of rows.
SQL SELECT TOP Clause Syntax
To retrieve the few records or to restrict the number of selected rows, we have to use the TOP Clause, followed by Expression or Percentage, and the syntax is
SELECT TOP Expression | Percentage [Column_Names] FROM [Table_Name]
We can use either the Expression or Percentage along with the SQL Server TOP clause. For instance, TOP 1 means it will retrieve the first record. If we write TOP 10 Percent, then the SELECT statement will extract 10% records from total records. You can also use the ORDER BY statement to sort the data accordingly. We use the below-shown table data to explain the Top Clause.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
TOP 1 Example
In this SQL Top clause example, We are going to select the first row from the customer’s table.
SELECT TOP 1 [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
The TOP Clause is retrieving the first row from the table.
Select TOP 10 Example
Returns the Top 10 rows from the customer’s table.
SELECT TOP 10 [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
TOP * Example
We can also use the * symbol instead of writing all the column names (Not Advisable). But, First, let’s see how the Top * clause works.
SELECT TOP 5 * FROM [Customer]
TOP ORDER BY Statement
In Tables, data will not be inserted in any order by default. So, If you want to retrieve the first three customers with the highest yearly income, then you have to sort the data using the ORDER BY statement. Next, you must use this Top Clause to get the first three customers.
SELECT TOP 5 [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
ORDER BY YearlyIncome DESC
Data will be sorted by the [Yearly Income] in descending order, then it will retrieve the Top five rows.
TOP Where and Order by Example
You can also use the Where Clause along with the Top clause to restrict the records selected by the select statement.
The following Where Clause query selects the first 10 records whose Sales are Greater than 1300. Remember, we also used the ORDER BY to sort the Server data using yearly income in Descending order.
SELECT TOP 10 [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
WHERE Sales > 1300
ORDER BY YearlyIncome DESC
TOP Percentage Example
We use TOP PERCENT to specify the number of customers the select query has to return. Here, we are using the first 40 PERCENT. It means 6 Rows because there are 15 rows on our customer’s table.
SELECT TOP 40 PERCENT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
Let us use the ORDER BY statement along with the TOP Percent.
SELECT TOP 40 PERCENT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
ORDER BY Sales DESC
TOP WITH TIES
In general, the below example will return the first 5 rows of the Customers with the highest yearly income. When we use WITH TIES followed by SQL TOP Clause, then it will return the top 5 records. Plus, all the records whose yearly income equals the last record (5th) of the select statement.
SELECT TOP 5 WITH TIES [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
ORDER BY YearlyIncome DESC
Although we selected the top 5 rows, the output displays 7 records. Because the yearly income of 5, 6, and 7 records are the same. If these are some more records with [YearlyIncome] = 80000, then they will display in the result window.
NOTE: WITH TIES only works when we use the ORDER BY statement; otherwise, the Server will throw an error.
