SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Nassan
Nassan
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 850
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!
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16410 Visits: 19081
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
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 1076
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)
Nassan
Nassan
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 850
thanks. i will blug in and see if that answers the question
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39154 Visits: 38521
You may get an error, misplaced parens and the sort is wrong.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28088 Visits: 39928
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
sqlbi.vvamsi
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 412
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39154 Visits: 38521
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7245 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search