T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE

Question

T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE

SQL learner22

Default port

Points: 1487
More actions
January 28, 2013 at 1:26 pm

#267812

PLEASE HELP ME WHO EVER CAN WRITE MORE EFFICIENT T-SQL THAT ANSWERS THE QUESTION.
THIS IS WHEN WE DONT KNOW THE SALARY INFORMATION AND WE ONLY CARE THE 10TH AND 11TH EMPLOYEES
ITS FROM JOB INTERVIEW, WHICH I SIAD ! I DONT KNOW!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

How about thinking you need the first 11 but you need to discard the first 9. How do you discard rows? EXCEPT, NOT IN, NOT EXISTS, OUTER JOIN?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Using the Rank function would be very helpfull for this., possibly a cte.

;WITH Salaries AS (SELECT

EmpID,

EmpLastName,

EmpFirstName,

Salary

RANK() OVER (PARTITION BY EmpID, ORDER BY Salary))

FROM dbo.Employee)AS SalaryRank

SELECT *

FROM Salaries

WHERE (SalaryRank = 10

OR SalaryRank=11)

SQL learner22 Default port Points: 1487 More actions · Answer 3

thanks. i will blug in and see if that answers the question

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

You may get an error, misplaced parens and the sort is wrong.

Lowell SSC Guru Points: 323518 More actions · Answer 5

you can also do it SQL2000 style(before the wonderful row_number/ranking functions came out) , using nested TOP statements to get the top 11, and then the top 2 of that in the opposite order.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 6

this should work too...

declare @t table(empname varchar(32),sal money)

insert into @t

select 'patrick',1000

union

select 'john',12000

union

select 'peter',500

union

select 'robert',360

union

select 'steve',810

union

select 'edward',3000

union

select 'sean',1200

union

select 'ricky',500

select * from(select empname,ROW_NUMBER() over (order by sal desc) as position from @t)a where position between 5 and 6

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

Hoping this is late enough not to be useful with your interview but soon enough to help you learn something:

WITH Salaries AS (

SELECT

EmpID,

EmpLastName,

EmpFirstName,

Salary,

rn = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Salary DESC)

FROM

dbo.Employee)

SELECT

*

FROM

Salaries

WHERE

rn between 10 and 11;

-- or, without windowing functions ala SQL Server 2000:

select top 2

*

from

(select top 11

EmpID,

EmpLastName,

EmpFirstName,

Salary

from

dbo.Employee

order by

Salary DESC) dt

order by

dt.Salary ASC;

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

Lynn Pettis (1/28/2013)
Hoping this is late enough not to be useful with your interview but soon enough to help you learn something

Why does this bring to mind the vision of an interviewee tapping away at an iPad during the interview, posting a question to SSC, in the hopes that it will be answered by the time the interviewer blinks twice?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St