SELECT TOP in SQL

In this tutorial you will learn and understand SELECT TOP query in SQL with practical examples. This SELECT TOP query will help you to get any numbers of rows from particular table. Please go through the whole article to understand it more.

What is SELECT top in SQL?

SELECT TOP command in SQL allows user to display the number of specified rows for particular SQL table. With the specified given number of rows count, SELECT TOP command display that rows only. It’s also possible to provide condition based output for SELECT TOP command in SQL. Example user want to display result Like top 10 rows, top 5 rows with employee salary greater then 50K etc…

Why we need SELECT TOP Command?

The SELECT TOP command is useful on large tables with thousands of records. This command mostly used to get the result when user need to see specific top related results based on WHERE clause or without WHERE condition. Therefore, the result set is limited to the first N number of ordered rows.

The SELECT TOP command is useful on large tables. If you would like to see few result from millions of records and you need to run simple SELECT Command in SQL query then it will take long time to run and sometime it will stop your SQL query window. So its better to use SELECT TOP command to see few results.

Below shows the syntax of the SELECT TOP clause with the SELECT statement:

Syntax:

SELECT TOP (number) 
      column_name(s)
FROM 
      table_name
WHERE 
      condition;

Lets take example to see the result with SELECT TOP command

Example #1: How to SELECT TOP 5 rows in SQL

In this example SELECT TOP query is  extracting top 5 first rows items with all columns in the table, As we have not selected any specific columns only we put “*” mark which means all columns, and it will display top 5 rows randomly from top rows in SQL table as we don’t have specifies any specific condition in the query.

SELECT TOP 5 
     * 
FROM 
   Customers_Tbl;

Result:

Example #2: SELECT TOP 10 Salary rows in decreasing Order

SELECT TOP in SQL can be use to select top 10 salary in decreasing order. Here you need to use the nested SELECT command and also GROUP BY and ORDER BY clause to perform this task

In below example you can see that we have second SELECT statement inside the WHERE clause.

and with in Where clause we have used SELECT TOP statement with GROUP BY and ORDER BY clause.

SELECT *
FROM table
WHERE
(
  salary IN
   (
    SELECT TOP (5) salary
    FROM table as table1
    GROUP BY salary
    ORDER BY salary DESC
   )
)

Example #3: SELECT TOP using WHERE clause

SELECT TOP in SQL can be performed on Where clause as well to show the result for TOP rows with condition on specific columns.

In below example WHERE clause on column [Province]= ‘Ontario’ extract only whose rows which is equal to ‘Ontario’

SELECT TOP 5 
      * 
FROM 
     Customers_Tbl
WHERE
     Province= 'Ontario';

Result:

Example #4: SELECT TOP in SQL using Order BY Clause

One more clause we recommend to use with SELECT TOP in SQL is ORDER BY clause which make your rows sorted by specific columns

SELECT TOP 10
       Customer ID, Customer Name, Province, region 
FROM
      Customers_Tbl
ORDER BY 
      Customer ID DESC;

Above example extract the TOP 10 mentioned columns sorted by [Customer ID] in descending order.

Hope in this tutorial, you have learned how to use the SELECT TOP in SQL statement to limit the number of rows or percentage of rows returned by a query.