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.
- By providing the number of records to return as part of the result set.
- 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.
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
|
1 2 |
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.
|
1 2 |
SELECT TOP 1 * FROM Customer WHERE CustomerGender="M"; |
Output:
| CustomerId | CustomerName | CustomerAge | CustomerGender |
|---|---|---|---|
| 1 | John | 31 | M |
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:
|
1 2 |
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:
|
1 2 |
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
Multiple SELECT TOP Statements
We can combine multiple SELECT TOP statements to get the desired result
Syntax:
|
1 2 |
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:
|
1 2 |
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