Tutorial Gateway

SQL TOP Clause

by

The SQL TOP Clause is used to restrict number of rows that are returned by the query. In general Database tables holds 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 TOP Clause and retrieve the required amount of rows.

For example, If you are Sales Manager for particular region and your job is to identify the top 10 highest performing products (Products with maximum Sales or Highest sales) then 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 TOP Clause followed by Expression or Percentage. In SQL, Syntax of the 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. For instance, TOP 1 means it will retrieve first record and 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 example.

-- Sql Select Top Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
  
FROM [Customer]

OUTPUT
SQL TOP Clause

SQL TOP Clause – Select All Columns

In this example, We are going to select the Top 5 rows from the customers table.

-- Sql Select Top Clause Example
SELECT TOP 5 [FirstName]
            ,[LastName]
            ,[Education]
            ,[Occupation]
            ,[YearlyIncome]
FROM [Customer]

OUTPUT
SQL TOP Clause 1

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

We can also use the * symbol instead of writing all the column name (Not Advisable). Lets see how it works.

-- Sql Server Top Clause Example
SELECT TOP 5 *

  FROM [Customer]

OUTPUT
SQL TOP Clause 3

SQL TOP Clause with ORDER BY Statement

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

-- Sql Select Top Clause Example
SELECT TOP 3 [FirstName]
            ,[LastName]
            ,[Education]
            ,[Occupation]
	    ,[YearlyIncome]
  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 three records.

OUTPUT
SQL TOP Clause 2

SQL TOP Clause with Percentage

In this example we are going to use PERCENT to specify the number of customers the query has to return. In the below Select statement we are using TOP 40 PERCENT it means 4 Rows because there are 10 records in our customers table.

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

OUTPUT

SQL TOP Clause 4

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

-- Sql Select Top Clause Example
SELECT TOP 40 PERCENT [FirstName]
                     ,[LastName]
                     ,[Education]
                     ,[Occupation]
	             ,[YearlyIncome]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC

OUTPUT
SQL TOP Clause 5

SQL Select Top Clause WITH TIES

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

-- Sql Select Top Clause Example
SELECT TOP 3 WITH TIES [FirstName]
                      ,[LastName]
                      ,[Education]
                      ,[Occupation]
                     ,[YearlyIncome]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC

OUTPUT
SQL TOP Clause 6

From the above screenshot you can observe that, Although we selected top 3 records output is displaying 6 records because yearly income of 3, 4, 5 and 6th records are same. If these are some more records with [YearlyIncome] = 70000 then it will display them as well.

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