Sunday, 10 February 2013

How to use Top Clause in SQL Server

SQL  Server TOP Clause
The TOP clause is used to specify the number of records to return in output from a Table.
The TOP clause can be very useful when we working 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-II :
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



Thanks,Tamatam

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts