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.

Viewing 14 posts - 16 through 28 (of 28 total)

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

Craig Farrell (11/9/2010)

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.

😀

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

scott.pletcher

SSCertifiable

Points: 5958

November 9, 2010 at 12:13 pm

#1248506

Does your book tell you what version of SQL Server? If it's SQL 2000, the answer will be different.

Scott Pletcher, SQL Server MVP 2008-2010

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

rko.37gags (11/9/2010)

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

You really wanna know?

I thought you would rather be happy with a spoon fed answer.

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

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

rko.37gags

Valued Member

Points: 64

November 9, 2010 at 12:20 pm

#1248512

Come on Dude....Just tell me.....what is "T" in that query

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

rko.37gags (11/9/2010)

Come on Dude....Just tell me.....what is "T" in that query

This is something called as a "derived table" which encapsulates the result of a SELECT statement in the FROM clause to another SELECT statement.

http://www.mssqltips.com/tip.asp?tip=1042

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

Also my solution will work for SQL 2005 and later.

I am assuming you are using a version greater than SQL 2000 since you have posted your question in SQL 2005 forum.With any version less than 2005 the query will be a lot different.

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

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

For the curious, question is from here:

www.scribd.com/doc/40561966/sql-Test.

Blocked by websense, I'm assuming it's a historical question/quiz.

How to find it?

http://www.google.com/#sclient=psy&hl=en&q=sql+%22top+2+salaries+for+each+employee%22&aq=f&aqi=&aql=&oq=&gs_rfai=&pbx=1&fp=eeb05746d54f919c

So, the answer they really want is:

Blah bla bla, blah blah. Blah BLAH blah blah... blah blah!

SELECT BLAH 2 BLAH FROM BLAH.BLAH.BLAH AS blah group by blah.blah having blah.blah >= 2.

Good Luck.

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

rko.37gags (11/9/2010)

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

The troll speaketh!!

- 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

WayneS SSC Guru Points: 95405 More actions · Answer 9

rko.37gags (11/9/2010)

what is rid and T

They are aliases. One is for a table, one for a column.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286562 More actions · Answer 10

I thought maybe they stood for no chance this person is qualified for the "DB_Developer" position and should therefore keep looking for the entry level position. 😛 You will not be successful in a new job that you are under qualified for. What are you going to do when asked to something as simple as this at work and you have no idea how to do it?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 11

Sean Lange (11/9/2010)

I thought maybe they stood for no chance this person is qualified for the "DB_Developer" position and should therefore keep looking for the entry level position. 😛 You will not be successful in a new job that you are under qualified for. What are you going to do when asked to something as simple as this at work and you have no idea how to do it?

That's obvious!

Put "Senior DBA" on the resume and hope the next company hires you fast.

- 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: 1000539 More actions · Answer 12

Yowch! So that's what a feeding frenzy on a newbie looks like from the outside. Lesson learned for me. 😉

--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)
Intro to Tally Tables and Functions

Geoff A SSChampion Points: 11511 More actions · Answer 13

Geoff A

SSChampion

Points: 11511

November 10, 2010 at 10:36 am

#1249046

Jeff Moden (11/10/2010)

Yowch! So that's what a feeding frenzy on a newbie looks like from the outside. Lesson learned for me. 😉

so you can teach an old dog new tricks 😛

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Geoff A (11/10/2010)

Jeff Moden (11/10/2010)

Yowch! So that's what a feeding frenzy on a newbie looks like from the outside. Lesson learned for me. 😉

so you can teach an old dog new tricks 😛

Absolutely. And a very good new trick it was at that.

--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)
Intro to Tally Tables and Functions