Given an employee table( with columns Emp_ID,Emp_Name,Emp_Salary), how would you find out the fifth highest salary?
-
Sorry Gus, I have no idea what ORLY means...
--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) -
Just a guess, but ORLY = Oh, Really.
Like I said, just a guess. :w00t:
😎
-
Gsquared must be a gamer 😉
-
Anders Pedersen (7/23/2008)
Gsquared must be a gamer 😉Yeah. But I don't use "Chatroom" as a language except for humor.
Yes, "ORLY" = "Oh, Really?", with a humorous overtone.
- 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
-
When did everyone become so efficient that they started using acronyms or abbreviations for everything?
Oh, sorry...
WDEBSITTSUAOAFE?
-
None mentioned about using the new rank , or row_number functions. It sure is possibe using them..
-
chandra.pottipati (7/23/2008)
None mentioned about using the new rank , or row_number functions. It sure is possibe using them..Sure it is. But I'm still not clear on if the OP wants the fifth highest salary or all employees with that salary.
DECLARE @Salarys TABLE
(
EmployeeID INT IDENTITY(1,1)
,Salary INT
)
INSERT INTO @Salarys
SELECT 100
UNION ALL SELECT 200
UNION ALL SELECT 300
UNION ALL SELECT 400
UNION ALL SELECT 150
UNION ALL SELECT 250
UNION ALL SELECT 350
UNION ALL SELECT 450
UNION ALL SELECT 125
UNION ALL SELECT 450
UNION ALL SELECT 450
UNION ALL SELECT 225
UNION ALL SELECT 325
UNION ALL SELECT 325
UNION ALL SELECT 325
UNION ALL SELECT 425
;WITH HighSal
AS(
SELECT dense_rank() over (order by salary desc) AS RANK
,EmployeeID
,Salary
FROM @Salarys
)
SELECT * FROM HighSal
WHERE RANK = 5
-
We are still waiting for the OP to provide more information before doing anything. All we can do at the moment is shoot in the dark, and that may not be helpful for the OP and just waste our time in the process.
😎
-
Sorry... all the ORLY stuff got me going.. 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Michael Earl (7/23/2008)
When did everyone become so efficient that they started using acronyms or abbreviations for everything?Oh, sorry...
WDEBSITTSUAOAFE?
Don't you mean: WDEBSETTSUAOAFE?
-
Given an employee table( with columns Emp_ID,Emp_Name,Emp_Salary), how would you find out the fifth highest salary?
with SalaryRank as
(
select Emp_id,Emp_name,Emp_salary,
dense_rank() over (order by Emp_salary desc) as SalaryRank
from Salaries
)
select *
from SalaryRank
Where SalaryRank = 5
This is not tested....
-
Michael Earl (7/23/2008)
When did everyone become so efficient that they started using acronyms or abbreviations for everything?Oh, sorry...
WDEBSITTSUAOAFE?
Hey, the Romans started it! It's all their fault, with their "SPQR" and stuff! Though, really, they probably stole the idea from the Greeks. The Romans were, after all, the original intellectual properties pirates. (Modern people download songs, the Romans illegally downloaded a whole religion, and tried to cover it up by changing the names!)
- 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
-
Rather than writing comples queries .... yo u can use the ranking functions... in sql server 2005 for this purpose:)
-
avanish (7/24/2008)
Rather than writing comples queries .... yo u can use the ranking functions... in sql server 2005 for this purpose:)Perfect... got a code example to go along with that suggestion? 😉
--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) -
Hope this will help you achieve your task,
create table aaaaa(id int)
insert into aaaaa values (1)
insert into aaaaa values (2)
insert into aaaaa values (3)
insert into aaaaa values (4)
insert into aaaaa values (5)
select * from aaaaa
with cte as
(
select row_number() over (order by id) as rownumber, id from aaaaa
)
select id from cte where rownumber = 5
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:http://venkattechnicalblog.blogspot.com/
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply