Top n clause

Top n clause

It is used to retrieve top ‘n’ no of rows from the result of the select statement.

Syntax:

Select top <n>  */<collist> from <tablename>

[ Where <condition>] [ group by <collist> ] [having <conditions>]

[Order by]

Display top 2 employees among highest salaried employees

select top 2  * from emp order by sal desc

Within the top ‘n’ clause you can use the keyword percent to retrieve only top n% of rows from the result of select statement.

Display top 20% of employees among highest salaried employee

select top 20 percent  * from emp order by sal desc

NOTE:

select top 2 with ties  * from emp order by sal desc

output:

5000.00      10

3000.00      20

3000.00      20

With top n th clause you may use the keyword “with ties” to display all rows from the table that have the same value as last row retrieved by the “topn” clause in the column used by order by.