Given an employee table( with columns Emp_ID,Emp_Name,Emp_Salary), how would you find out the fifth highest salary?

Question

Post reply

Given an employee table( with columns Emp_ID,Emp_Name,Emp_Salary), how would you find out the fifth highest salary?

steven-733381

Say Hey Kid

Points: 701
More actions
July 21, 2008 at 5:52 am

#194176

Hi Friends,
Given an employee table( with columns Emp_ID,Emp_Name,Emp_Salary), how would you find out the fifth highest salary?
Please tell

Viewing 15 posts - 1 through 15 (of 41 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You may get a better response with some sample data and showing an attempt at doing this yourself.

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Like this:

Select Top 1 * From (

Select Top 5

Emp_ID,Emp_Name,Emp_Salary

From Employee

Order By Emp_Salary

) Order By Emp_Salary DESC

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 3

It depends.

What if there are ties? Do you want the fifth record, even if there are five ties for highest?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kaushal SSC Enthusiast Points: 118 More actions · Answer 4

GSquared (7/21/2008)
It depends.
What if there are ties? Do you want the fifth record, even if there are five ties for highest?

I think the OP wants to know the fifth highest salary, NOT the record with the 5th highest salary.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Kaushal (7/21/2008)
GSquared (7/21/2008)
It depends.
What if there are ties? Do you want the fifth record, even if there are five ties for highest?
I think the OP wants to know the fifth highest salary, NOT the record with the 5th highest salary.

Yes, but since the solution depends on how to treat ties, till he replies, I can't expand on that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Hard to tell from the question alone. Michael Earl is right, we need to know more from the OP before we can really answer the question.

😎

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

Michael Earl-395764

SSC Guru

Points: 53873

July 21, 2008 at 12:34 pm

#845955

I am forced to agree with me as well.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Heh... look how many people are doing homework with no try from the op... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Actually, most of us have just asked for more from OP.

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Dang... you're right Lynn... I was blazing through a bunch of these and didn't read all the posts. My appologies. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 11

Michael Earl-395764

SSC Guru

Points: 53873

July 22, 2008 at 6:33 am

#846357

I'm glad I'm not the only one that does that

GSquared SSC Guru Points: 260824 More actions · Answer 12

Michael Earl (7/22/2008)
I'm glad I'm not the only one that does that

The only one that does what? 🙂 🙂

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

He was talking about me blazing through posts and missing stuff...:)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 14

Jeff Moden (7/22/2008)
He was talking about me blazing through posts and missing stuff...:)

ORLY? (That was the whole point of my post.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon