SQL TOP Clause

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, the SELECT statement will extract 10% of the 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.

SQL TOP 1 Example

In this Top clause example, we will select the first row from the customer’s table.

SELECT TOP 1 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

The TOP Clause retrieves the first row from the table.

SELECT TOP 1 Record from customers Clause Example

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 10 Records Example 3

SQL Server 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]

SQL Top and Order By Clause 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, you must 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.

Where and Order by Example

You can also use the Where Clause and 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
SQL TOP Clause Order By and Where

Percentage Example

We use SQL Server 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

WITH TIES

The example below will return the first 5 rows of the Customers with the highest yearly income. When we use WITH TIES followed by TOP Clause, 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, they will display in the result window.

TOP Clause with ties

NOTE: WITH TIES only works when we use the ORDER BY statement; otherwise, the Server will throw an error.

Categories SQL