query

Question

Post reply

query

manikanta1207

SSC Enthusiast

Points: 199
More actions
July 3, 2013 at 12:21 pm

#279946

what is the query to select nth highest salary of emp table

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Keejli69 Ten Centuries Points: 1307 More actions · Answer 1

manikanta1207 (7/3/2013)
what is the query to select nth highest salary of emp table

select MAX(Salary) from Employee;

Lowell SSC Guru Points: 323518 More actions · Answer 2

elee1969 (7/3/2013)
manikanta1207 (7/3/2013)
what is the query to select nth highest salary of emp table
select MAX(Salary) from Employee;

this really sounds like homework....

anyway,

the nth highest , ie the second or third, requires the use if a subquery and row_number, or nested top X queries with opposite ORDER BY commands.

SELECT * FROM (select row_number() over(partition by order by.....) myAlais Where RW = 3

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

manikanta1207 SSC Enthusiast Points: 199 More actions · Answer 3

manikanta1207

SSC Enthusiast

Points: 199

July 3, 2013 at 1:01 pm

#1629648

give me an ex please

Lowell SSC Guru Points: 323518 More actions · Answer 4

manikanta1207 (7/3/2013)
give me an ex please

because this seems a lot like homework, i only offered a partial SQL to help you think about the issue;

this doesn't pass a syntax check but provides a couple of clues as to how to tackle the issue

SELECT * FROM (select row_number() over(partition by order by.....) As RW FROM SOMETABLE ) myAlias Where RW = 3

if you can show your work, and tell us what isn't working, we could help you understand the concepts behind solving the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Uhhh... Isn't this a case of using RANK() or DENSE_RANK(), depending on how ties are to be counted?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St