Different ways to find TOP N records.

For our explanation we have taken a table ‘parent_tab ‘ having the following seven records data.

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Various ways to achieve this : Our objective is to find first 4 records from the above table

1. Using ROWCOUNT

set rowcount 4

select name from parent_tab

set rowcount 0

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

2. Using ROW_NUMBER

select name from

(

select row_number() over (order by first_id) as rno, name from parent_tab

) as tab where rno <= 4

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

3. Using Top N

select top 4 name from parent_tab

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

If you know any other way to achieve this, please light up your suggestion to me.

Post Reference: Vikram Aristocratic Elfin Share