Post reply

Using the salary table on the left, write a SQL Server T-SQL query to find the Top 2 salaries for each employee. The result of the query is on the right.

  • November 9, 2010 at 9:11 am

    #226234

    Employee id Salary Employee id salary

    12 12000 12 13000

    12 10000 12 12000

    12 13000 10 23000

    10 23000 10 20000

    10 20000 4 6000

    10 14000 4 4000

    4 2000

    4 6000

    4 4000

  • November 9, 2010 at 9:38 am

    #1248385

    you should include your question in the body of your post not the heading.

    To do this query you should have a look at ROW_NUMBER

  • November 9, 2010 at 9:41 am

    #1248389

    If u dont mind....Can u post query

  • November 9, 2010 at 9:47 am

    #1248394

    rko.37gags (11/9/2010)

    If u dont mind....Can u post query

    That would be easier to do if you posted the question.

  • November 9, 2010 at 10:20 am

    #1248424

    Question: Using the salary table on the left, write a SQL Server T-SQL query to find the Top 2 salaries for each employee. The result of the query is on the right.

  • November 9, 2010 at 10:27 am

    #1248431

    rko.37gags (11/9/2010)

    Question: Using the salary table on the left, write a SQL Server T-SQL query to find the Top 2 salaries for each employee. The result of the query is on the right.

    Since we can't see the table on the left, or the results on your right, in the textboook, you need to screen shot the page, post it somewhere in an image hosting and... read my sig.

    As already mentioned, you want to look into Row_Number(). A CTE will probably help here too.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 9, 2010 at 10:31 am

    #1248434

    Actually a cross apply with Top 2 and Order By Desc will probably perform better than a CTE with a row number in it. Not sure, but it usually does.

    I'm willing to post a query if you're willing to let your teacher/professor know that you cribbed it.

    - 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

  • November 9, 2010 at 11:34 am

    #1248471

    Plz post me the query.....i didnt understand ur "Blah Blah"

  • November 9, 2010 at 11:44 am

    #1248475

    rko.37gags (11/9/2010)

    Plz post me the query.....i didnt understand ur "Blah Blah"

    OK.So here is the answer

    select * from table

    A "Blah Blah" answer for a "Blah Blah" question posted by "Blah Blah" idiot.

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • November 9, 2010 at 11:52 am

    #1248483

    Craig Farrell (11/9/2010)

    Since we can't see the table on the left, or the results on your right, in the textboook, you need to screen shot the page, post it somewhere in an image hosting and...

    Select Top 1 Answers from BackOfTheBook WHERE page = @CurrentPage

    -- Cory

  • November 9, 2010 at 11:55 am

    #1248484

    i attached the excel file

  • November 9, 2010 at 12:05 pm

    #1248496

    Question: Using the salary table on the left, write a SQL Server T-SQL query to find the Top 2 salaries for each employee. The result of the query is on the right.

    ID Sal Result

    3 1200 3 8000

    3 1700 3 1700

    3 8000 6 4000

    6 3000 6 3700

    6 3700 4 6000

    6 4000 4 4000

    4 2000

    4 6000

    4 4000

  • November 9, 2010 at 12:05 pm

    #1248498

    rko.37gags (11/9/2010)

    i attached the excel file

    Now that's better.

    Below is your query.

    select * from

    (

    select *,row_number()over(partition by employeeid order by salary desc)rid from salary

    )T where rid<=2

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • November 9, 2010 at 12:08 pm

    #1248499

    Sachin.Nandanwar (11/9/2010)

    rko.37gags (11/9/2010)

    i attached the excel file

    Now that's better.

    Below is your query.

    select * from

    (

    select *,row_number()over(partition by employeeid order by salary desc)rid from salary

    )T where rid<=2

    You're nicer than I am, Sachin. I fear I would be forced to Blah Blah some more.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 9, 2010 at 12:10 pm

    #1248502

    Thanks for ur reply....Some doubts..

    select * from

    (

    select *,row_number()over(partition by employeeid order by salary desc)rid from salary

    )T where rid<=2

    what is rid and T

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

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