Tutorial Gateway

SQL TOP Clause

by

The SQL TOP Clause is used to restrict the number of rows that are returned by the query. In general Database, tables hold millions or billions of records and If you want to see the top 10 products then, there is no point in retrieving all the 1 million records. We can simply use the SQL TOP Clause and retrieve the required amount of rows.

For example, If you are Sales Manager for a particular region and your job is to identify the top 10 highest performing products (Products with maximum Sales or Highest sales) then you can use SQL Top Clause. For this, you can write the query as Select Top 10 Product Name from Products Table Order By Sales in Descending Order. By seeing the result you can understand that x is performing well, Using this data you can request the management to increase the production of that 10 products.

SQL SELECT TOP Clause Syntax

To retrieve the few records or to restrict the number of rows we have to use the SQL TOP Clause followed by Expression or Percentage.

The Syntax of the Sql Server TOP Clause is

-- SQL TOP Clause Syntax
SELECT TOP Expression | Percentage [Column_Names]

FROM [Table_Name]

From the above syntax, you can observe that We can use either the Expression or Percentage along with the TOP Clause in Sql Server.

For instance, TOP 1 means it will retrieve the first record. If we write TOP 10 Percent then SELECT Statement will retrieve 10% records from total records. You can also use the ORDER BY statement to sort the data accordingly.

We are going to use the below-shown data to explain the Select Top Clause in SQL Server with an example.

-- SQL Server Top Clause Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

OUTPUT

SQL TOP Clause 1

SQL TOP 1 Example

In this SQL Top clause example, We are going to select the Top 1 row from the customer’s table.

-- SQL Server Top 1 Example
SELECT TOP 1 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

OUTPUT
SQL TOP Clause 2

From the above screenshot, you can observe that the SQL TOP Clause is retrieving the first record from the table.

SQL TOP 10 Example

This example returns the Sql Server Top 10 rows from the customer’s table.

-- SQL Server Top 10 Example
SELECT TOP 10 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

OUTPUT

SQL TOP Clause 3

SQL TOP * Example

We can also use the * symbol instead of writing all the column name (Not Advisable). Let’s see how the SQL Top * works.

-- SQL Server Top * Example
SELECT TOP 5 *
  FROM [Customer]

OUTPUT
SQL TOP Clause 4

SQL TOP ORDER BY Statement

In Tables, By default data will not be inserted in any order. So, If you want to retrieve the Top three customers who are having the highest yearly income then you have to sort the data using ORDER BY statement. Next, you have to use this SQL Top Clause to get the top three customers

-- SQL Server Top Order By Example
SELECT TOP 5 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY YearlyIncome DESC

Now the data will be sorted by the [Yearly Income] in the descending order then it will retrieve Top five records.

OUTPUT
SQL TOP Clause 5

SQL TOP Where Example

You can also use Where Clause along with the Sql Server Top clause to restrict the records selected by the select statement.

The following query selects the Top 10 records whose Sales is Greater than 1300. Remember, we also used the ORDER BY to sort the data using yearly income in Descending order.

-- SQL Server Top Clause Example
SELECT TOP 10 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE Sales > 1300
  ORDER BY YearlyIncome DESC

OUTPUT

SQL TOP Clause 6

SQL TOP Percentage Example

In this example, we are going to use SQL TOP PERCENT to specify the number of customers the query has to return. In the below Select statement we are using SQL TOP 40 PERCENT it means 6 Rows because there are 15 records in our customer’s table.

-- SQL Server Top Clause Example
SELECT TOP 40 PERCENT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

OUTPUT

SQL TOP Clause 7

Let us use the ORDER BY statement along with the TOP Percent in Sql Server.

-- SQL Server Top Clause Example
SELECT TOP 40 PERCENT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY Sales DESC

OUTPUT
SQL TOP Clause 8

SQL TOP WITH TIES

In general, Below example will return top 5 records of the Customers with the highest yearly income. When we use WITH TIES followed by TOP Clause in SQL Server, then it will return top 5 records plus all the records whose yearly income is equal to the last record (5th) of the select statement.

-- SQL Server Top Clause Example
SELECT TOP 5 WITH TIES [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY YearlyIncome DESC

OUTPUT

SQL TOP Clause 9

From the above screenshot, you can observe that, Although we selected top 5 records output is displaying 7 records. Because the yearly income of 5, 6 and 7 records are same. If these are some more records with [YearlyIncome] = 80000 then they will display in the result window.

NOTE: WITH TIES only work when we use the ORDER BY statement otherwise it will throw an error

Thank You for Visiting Our Blog

Placed Under: SQL