SQL Select Top

Filed Under: SQL Server

SQL Select Top

SQL SELECT TOP clause is very useful when dealing with a huge set of data or tables with a huge amount of data.

This clause is used to restrict the number of records to return a result set.

The SELECT TOP clause can be used in two ways.

  1. By providing the number of records to return as part of the result set.
  2. Providing the percentage of the number of records to return as part of the result set.

We will try to understand both the usage in detail in below-mentioned sections.

Select Top clause is not supported by MySQL and Oracle. In MySQL, you can use the LIMIT clause to select a limited number of rows. In Oracle, ROWNUM does the same job. However, it’s supported by SQL Server and all the examples given here will work on SQL Server database.

SQL Select Top – Number of Records to Return

We will try to understand the syntax for using SQL SELECT TOP by providing the number of records to return as the result set.

Select Top Syntax

SELECT TOP number column_name FROM table_name WHERE condition;

In the syntax above all the columns data is retrieved based on the WHERE clause and restricted by the number that is provided as part of the SELECT TOP.

Let’s consider the following Customer Table to understand SELECT TOP command for copying all the columns data based on a condition.

Customer:

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
3 Annie 35 F
4 Tom 38 M

Scenario:

Get the first-row data from Customer table where gender is male.

SELECT TOP 1 * FROM Customer WHERE CustomerGender = 'M';

Output:

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
SQL Top Number Records

SQL Top Based on Number of Records

SQL Select Top Percent Of Records to Return

We will try to understand the syntax for using SQL SELECT TOP by providing the percentage of records to return as the result set.

Syntax:

SELECT TOP number PERCENT column_name FROM table_name WHERE condition;

In the syntax above all the columns data is retrieved based on the WHERE clause and restricted by the percent that is provided as part of the SELECT TOP.

Let’s consider the following Customer Table to understand SELECT INTO command for copying all the columns data based on a condition.

Scenario:

Get 50% rows data from Customer table where gender is male.

Query:

SELECT TOP 50 PERCENT * FROM Customer WHERE CustomerGender = 'M';

Output:

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
SQL Top Based on Percent

SQL Top Based on Percent

Multiple SELECT TOP Statements

We can combine multiple SELECT TOP statements to get the desired result

Syntax:

SELECT TOP number column_name FROM table_name WHERE condition (the select statement with another SELECT TOP);

Scenario:

Get the first-row data from Customer table where gender is male.

Query:

SELECT TOP 1 * FROM Customer WHERE CustomerAge = (SELECT TOP 1 CustomerAge FROM Customer ORDER BY CustomerAge desc);

Output:

CustomerId CustomerName CustomerAge CustomerGender
4 Tom 38 M
SQL Multiple Top

SQL Multiple Top

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages