Select nth Maximum paid Employee

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select nth Maximum paid Employee

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-10 : 01:06:50

Hi all,
I was asked in an interview that How will select the nth maximum paid employee using SQL Server 2000/2005 Mode.

Thanks
Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 01:09:50

use the row_number() function

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-10 : 01:18:49

Thanks Khtan,

I tried like this


select Salary,Row_Number() Over(order by salary desc) as 'RowNumber' from Employee

it produced the result as


Salary  RowNumber
1026751	1
36506	2
14400	3
8416	4

Now I added this condtion


select Salary,Row_Number() Over(order by salary desc) as 'RowNumber' from Employee 
where RowNumber=4

and the error is
Msg 207, Level 16, State 1, Line 2
Invalid column name 'RowNumber'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 03:45:59

[code]SELECT Salary
from
(
SELECT Salary,Row_Number() OVER(ORDER BY salary DESC) AS 'RowNumber'
FROM Employee
) e
WHERE RowNumber = 4[/code]

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-10 : 03:55:39

Can you Please explain this part.
[quoate]
) e
WHERE RowNumber = 4
[/Quoate] and will work in 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 03:59:02

quote:
Originally posted by danasegarane76

Can you Please explain this part.
[quoate]
) e
WHERE RowNumber = 4
[/Quoate] and will work in 2000

table alias for the derived table.

No. row_number() only avaiable in SQL Server 2005 not 2000.

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-10 : 04:01:27

What to do in SQL Server :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 04:32:30

quote:
Originally posted by danasegarane76

What to do in SQL Server :)

what do you mean ?

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-10 : 04:37:25

Sorry for that post. What do in SQL 2000

Kristen
Test

22859 Posts

Posted - 2007-11-10 : 05:42:16

Start with #2 here:

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 02:39:30

My favourite

Select min(col) from
(
select Top N col from table order by col DESC
) as t

Madhivanan

Failing to plan is Planning to fail

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-12 : 02:51:11

Select min(col) from
(
select Top N col from table order by col DESC
) as t

WRONG ! if col is not unique :)

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-12 : 03:35:35

Thank U :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 03:42:05

Why? The question is not the 5th unique salary?

Select min(col) from
(
select distinct Top N col from table order by col DESC
) as t

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 03:49:00

quote:
Originally posted by evilDBA

Select min(col) from
(
select Top N col from table order by col DESC
) as t

WRONG ! if col is not unique :)

Yes it is, because OP didnt specify unique

Madhivanan

Failing to plan is Planning to fail

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-11-15 : 23:25:26

Thanks Once Again Gurus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources