Summary -

In this topic, we described about the Select Top with detailed example.

While dealing with large number of rows in table, we can get multiple number of rows in result-set. If we want to fetch only specific rows, we can use SELECT TOP statement. By using SELECT TOP Statement, we can get as many as specific rows from the result-set.

Syntax -

SELECT TOP Number| Percent Column1,Column2,..
 FROM Table_name [WHERE Condition];

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting specified rows from table.

Requirement - Get first 4 rows from employee_details table. The query was as follows – 

SELECT TOP 4 * FROM employee_details;

By executing above query, we can get first 4 rows from employee_details table. The output was as shown in below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000

Scenario – Get specified rows with specified columns from table.

Requirement – Get emp_id, emp_name, designation of first four rows from employee_details table. The query was as follows – 

 SELECT TOP 4 emp_id, emp_name, designation FROM employee_details;

By executing above query, we can get first 4 rows with specified columns from employee_details table. The output was as shown in below –

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager

Scenario – Get specified percent rows from table.

Requirement – Get first 50% of total rows from employee_details table. The query was as follows – 

SELECT TOP 50 PERCENT * FROM employee_details;

By executing above query, we can get first 50% rows from employee_details table. The output was as shown in below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000

Scenario – Get specified percent rows with specified columns from table.

Requirement - Get emp_id, emp_name, designation columns from first 50% of total rows in employee_details table. The query was as follows – 

SELECT TOP 50 PERCENT emp_id, emp_name, designation 
FROM employee_details;

By executing above query, we can get 50 percent rows of specified columns from employee_details table. The output was as shown in below –

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst

Scenario – Get specified percent rows with specified columns with WHERE clause.

Requirement - Get 50% rows emp_id, emp_name, designation details from employee_details table from department 1000. The query was as follows – 

SELECT TOP 50 PERCENT emp_id, emp_name, designation 
FROM employee_details WHERE dept_id = 1000;

By executing above query, we can get 50 percent rows from employee_details table under the dept_id 1000. The output was as shown in below –

emp_id emp_name designation
001 Employee1 Director
003 Employee3 Manager
005 Employee5 Analyst

Scenario – Get specified rows with specified columns from table by using WHERE clause.

Requirement – Get first 2 emp_id, emp_name, designation details from employee_details table from department 2000. The query was as follows – 

SELECT TOP 2  emp_id, emp_name, designation
FROM employee_details WHERE dept_id = 2000;

By executing above query, we can get first 2 rows with emp_id, emp_name, designation details from employee_details table belongs dept_id 2000. The output was as shown in below –

emp_id emp_name designation
002 Employee2 Director
004 Employee4 Manager