nth maximum - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

nth maximum

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-11 : 07:57:18

subha writes "how to get the nth maximum say 'nth maximum salary' out of the records available using the 'top' keyword. For example, I have say n records in the emp table and i need to get the 7th maximum salary out of the available records."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-11 : 08:01:58

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

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 08:12:16

Ask your professor, he get paid to answer your questions.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-12 : 05:33:01

1 Do Google search
2 Replace N by number

Select min(sal) from
(
    Select Top N sal from table order by sal DESC
) T

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 02:30:49

or try this...

Select Max(Sal) From Table Where Sal < (Select Max(Sal) From Table where Sal < (Select Max(Sal) From Table...))))))) upto 7 steps. u will get 7th latgest salary.

Mahesh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-14 : 06:14:10

and why would you want to do something as inefficient as that mahesh?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-14 : 06:42:35

Irony, maybe?

select * from <yourtablenamehere>
where row_number() over (order by salary desc) = 7

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 06:43:09

quote:
Originally posted by spirit1

and why would you want to do something as inefficient as that mahesh?

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

1 It was not suggested here
2 He is simulating the query that is used to Find 2nd Maximum
3 Mahesh, set the execution plan and check

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 06:46:43

quote:
Originally posted by Peso

Irony, maybe?

select * from <yourtablenamehere>
where row_number() over (order by salary desc) = 7

Peter Larsson
Helsingborg, Sweden

So the questioner should use SQL Server 2005?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-14 : 06:51:24

Not very likely, but since it doesn't say in th OP, any solution is viable.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 09:00:30

No worries