Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:01 PM
Points: 36, Visits: 716
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!
Post #1412625
Posted Monday, January 28, 2013 1:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 3,354, Visits: 7,255
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1412629
Posted Monday, January 28, 2013 1:37 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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)
Post #1412630
Posted Monday, January 28, 2013 1:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:01 PM
Points: 36, Visits: 716
thanks. i will blug in and see if that answers the question
Post #1412642
Posted Monday, January 28, 2013 1:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
You may get an error, misplaced parens and the sort is wrong.



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)
Post #1412645
Posted Monday, January 28, 2013 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 12,887, Visits: 31,833
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1412646
Posted Monday, January 28, 2013 3:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 26, 2014 7:17 PM
Points: 54, Visits: 349
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

Post #1412679
Posted Monday, January 28, 2013 10:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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;





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)
Post #1412752
Posted Tuesday, January 29, 2013 12:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 3,614, Visits: 5,228
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!
Post #1412787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse