Top Statement in T-SQL

Hi,

Today we will discuss TOP statement of T-SQL. What all we can do with TOP statement and where all we can apply TOP clause?

Top clause is mandatory whenever we need to use order by statement while creating a view.
The TOP clause limits the number of rows returned in a result set. SQL Server 2005 has added usage of expression in TOP clause earlier it used to be a constant value. The TOP clause can be used in SELECT, INSERT, UPDATE and DELETE statements.

Suppose we have a table with duplicate rows, earlier we used to come up with a quite complex query to retrieve that single row, either using SET ROWCOUNT 1 or some other query, now

DELETE top 1 from table.

This will delete the 1st row from the table.

TOP used as expression

Declare @t int
Set @t = 10
Select top @t from table.

Declare @v int
set @v=100
Select top @v/@t from table


--Update statment

update top (1) test --table name
set name='aa'

This will update 1st row of the table and set the name ='aa'

With TIES example:

Select top 10 * WITH TIES from table 1 order by Employee ID

With TIES clause causes the SELECT TOP statement to return an additional X rows where EmployeeID is same as EmployeeID of 4th field.

Enjoy SQL coding.