Sunday, 10 February 2013

SQL TOP Clause Syntax and Examples

SQL  Server TOP Clause
The TOP clause is used to specify the number of records to return.
The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Syntax
SELECT TOP number|percent column_name(s) FROM table_name

Example :

Let us suppose we have an EMPLOYEE Tables as follows :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
18000
1008
Varun
Team Lead
1
33000
1009
Smith
Team Lead
3
50000


-----------------------------------------------------------------------------------------------------------
Criteria-I : Now we want to select only the first two  records from the table above.

SELECT TOP 2 * From EMPLOYEE

Result :

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000



Now we want to select only the first two  records order by salary.

SELECT TOP 2 * From EMPLOYEE Order By Salary

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1003
Ravi Kumar Rao
Analyst
1
10000
1005
Sai Kumar Teja
Analyst
2
15000

-----------------------------------------------------------------------------------------------------------
Criteria-I :
Now we want to select only 50% of the records in the table above.We use the following SELECT statement:

SELECT TOP 50 PERCENT *From EMPLOYEE

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000



Note: 
All database systems may not support the TOP clause.

Thanks.,
TPR

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts