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.

Question

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.

rko.37gags

Valued Member

Points: 64
More actions
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

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

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

rko.37gags Valued Member Points: 64 More actions · Answer 2

rko.37gags

Valued Member

Points: 64

November 9, 2010 at 9:41 am

#1248389

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

rko.37gags (11/9/2010)
If u dont mind....Can u post query

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

rko.37gags Valued Member Points: 64 More actions · Answer 4

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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

GSquared SSC Guru Points: 260824 More actions · Answer 6

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

rko.37gags Valued Member Points: 64 More actions · Answer 7

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 8

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

Cory E. SSCertifiable Points: 7076 More actions · Answer 9

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

rko.37gags Valued Member Points: 64 More actions · Answer 10

rko.37gags

Valued Member

Points: 64

November 9, 2010 at 11:55 am

#1248484

i attached the excel file

rko.37gags Valued Member Points: 64 More actions · Answer 11

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 12

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

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.