Post reply

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

  • July 27, 2008 at 1:00 am

    #848866

    Hope this code will help you,

    create table aaaaa(id int)

    insert into aaaaa values (1)

    insert into aaaaa values (2)

    insert into aaaaa values (3)

    insert into aaaaa values (4)

    insert into aaaaa values (5)

    select * from aaaaa

    with cte as

    (

    select row_number() over (order by id) as rownumber, id from aaaaa

    )

    select id from cte where rownumber = 5

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • July 27, 2008 at 8:52 am

    #848897

    I was looking for Avinish to provide the code example since he also made the suggestion with no code. 😉

    --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)

  • July 28, 2008 at 3:56 am

    #849025

    select min(empsalary) from emptable where empsalary in (select top 5 empsalary from emptable order by empsalary desc).

    try this one....

  • July 28, 2008 at 5:56 am

    #849099

    How about this query...

    Select Top 1 TrainingPlanID from

    (Select Top 90 Percent TrainingPlanID from

    (Select top 5 TrainingPlanID from tblTrainingPlan Order by TrainingPlanID DESC) Q1

    order By TrainingPlanID ASC ) Q2

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • July 28, 2008 at 8:07 am

    #849206

    Atif: Why 3 layers? I don't think that you need the middle layer, "TOP 90 percent" doesn't do anything for you and the "Order By .. ASC" can be moved to the outer layer.

    [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]

  • July 29, 2008 at 1:59 am

    #849597

    101 % right... I wonder why I didnt think of it. Thanks

    here it goes...

    Select Top 1 TrainingPlanID from

    ( Select top 5 TrainingPlanID from tblTrainingPlan Order by TrainingPlanID DESC )

    Q2 order By TrainingPlanID ASC

    thanks...

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • July 30, 2008 at 10:57 am

    #850696

    Silly question - why in the blazing heck are we looking for the 5th highest salary? I'm struggling to find an application for this.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • July 30, 2008 at 2:46 pm

    #850869

    jcrawf02 (7/30/2008)

    Silly question - why in the blazing heck are we looking for the 5th highest salary? I'm struggling to find an application for this.

    Maybe it's a game! If you're in the top five, you get an award. Everyone else is just a loser! (Just to maintain the usefulness and seriousness of this particular thread.)

    - 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

  • July 30, 2008 at 2:50 pm

    #850871

    GSquared (7/30/2008)

    jcrawf02 (7/30/2008)

    Silly question - why in the blazing heck are we looking for the 5th highest salary? I'm struggling to find an application for this.

    Maybe it's a game! If you're in the top five, you get an award. Everyone else is just a loser! (Just to maintain the usefulness and seriousness of this particular thread.)

    See, now that would make sense, if you returned all top five. I guess we could be looking for the threshold at which you're NOT a loser?

    Or is this one of those interview questions everyone is always looking for? Answer: who cares who number five is, either you're number one, or you're working for them.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • July 30, 2008 at 3:18 pm

    #850878

    jcrawf02 (7/30/2008)

    GSquared (7/30/2008)

    ...who cares who number five is, either you're number one, or you're working for them.

    If you aren’t the lead dog, the view never changes.

  • July 30, 2008 at 4:13 pm

    #850904

    jcrawf02 (7/30/2008)

    Silly question - why in the blazing heck are we looking for the 5th highest salary? I'm struggling to find an application for this.

    Hmm, I seem to recall that all corporations must report the X-highest employee salaries to the SEC. Perhaps X=4 and some aspiring Soprano-like "legitmate businessman" wants to know how much he can take in kickback salary without attracting attention? 😀

    [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]

  • August 5, 2008 at 8:07 am

    #853192

    Of course, the really amazing thing is, here we are on page 5 of this thread, and the original poster still hasn't given us the data necessary to answer his original question, so we still haven't answered it. I guess I/we have enough spare time on our hands after all! 🙂

    - 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

Viewing 12 posts - 31 through 41 (of 41 total)

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