May 12, 2009 at 7:20 am
Hi all
I want to knw how i can retrieve the second highest value from the table which has some data
like the detail of employe and i want to retrieve the name of the employe who has second highest salary in the detail table
pls its very urgent
reply soon
thanks in advance/
May 12, 2009 at 7:34 am
you should be able to use ROW_NUMBER() for this, have a look at BOL for the syntax, or post your table defs and some sample data for further help..
May 12, 2009 at 7:36 am
Try this:
DECLARE @employees TABLE (
empId int,
name varchar(50)
)
DECLARE @salaries TABLE (
empId int,
salary money
)
INSERT INTO @employees VALUES (1, 'John')
INSERT INTO @employees VALUES (2, 'Mary')
INSERT INTO @employees VALUES (3, 'Jane')
INSERT INTO @employees VALUES (4, 'Leo')
INSERT INTO @salaries VALUES (1, 1000)
INSERT INTO @salaries VALUES (2, 2000)
INSERT INTO @salaries VALUES (3, 3000)
INSERT INTO @salaries VALUES (4, 4000)
SELECT emp.empId, emp.Name, Sqry.salary
FROM @employees AS emp
INNER JOIN (
SELECT TOP 1 empId, salary
FROM (
SELECT TOP 2 empId, salary
FROM @salaries
ORDER BY salary DESC
) AS TopTwo
ORDER BY salary ASC
) AS Sqry
ON emp.empId = Sqry.empId
-- Gianluca Sartori
May 12, 2009 at 7:37 am
WITH max_salaries (Salary) AS
( SELECT TOP 2 Salary FROM Workers ORDER BY Salary DESC ) -- returns the two higher salaries
SELECT min(Salary) FROM max_salaries -- returns the lower salary from the two selected above: his is actually the second highest value
May 12, 2009 at 7:37 am
Check out the RANK function in BOL. Something like this might work (Uses adventureworks):
;WITH cteRank AS
(
SELECT
EPH.EmployeeID,
EPH.Rate,
RANK() OVER(ORDER BY Rate desc) AS ranking
FROM
HumanResources.EmployeePayHistory AS EPH JOIN
(
SELECT
EmployeeID,
MAX(RateChangeDate) AS last_date
FROM
HumanResources.EmployeePayHistory AS EPH2
GROUP BY
EPH2.EmployeeID
) AS LastRate ON
EPH.EmployeeID = LastRate.EmployeeID AND
EPH.RateChangeDate = LastRate.last_date
)
SELECT
*
FROM
cteRank
WHERE
ranking = 2
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 12:39 am
anu1krishna (5/12/2009)
pls its very urgent
Hey,
Have you considered the possible benefits of having a go at these (presumably homework) questions?
Sure posting on here and kicking back may seem cool, but how much are you actually learning...?
Just a thought!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 16, 2009 at 11:48 pm
i tried like this
declare @id int
set @id=select top 2 id_num from new_employes order by id_num desc
select id_num from new_employes where id_num=(select min(@id)from new_employes)
but i am getting error please say what is incorrect in it
May 17, 2009 at 1:45 am
anu1krishna (5/16/2009)
i tried like thisdeclare @id int
set @id=select top 2 id_num from new_employes order by id_num desc
select id_num from new_employes where id_num=(select min(@id)from new_employes)
but i am getting error please say what is incorrect in it
Everything seems to be wrong with this query.....
1. set @id=select top 2 id_num from new_employes order by id_num desc
You can only store a single value in a variable declared as int.
2.select id_num from new_employes where id_num=(select min(@id)from new_employes)
Since you cant store more than one value in the variablr @id, how can you apply min function on that variable.......
3. and plenty more errors ......
My suggestion is ...Get your basics right....!
May 17, 2009 at 5:25 am
thx for ur suggestion:w00t:
May 17, 2009 at 5:56 am
thx fro your quick reply it is really a good idea to use rank function
May 17, 2009 at 5:57 am
thx dear it really works fine 🙂
May 18, 2009 at 9:58 am
why not just grab the TOP 2? and choose the min of the 2?
May 18, 2009 at 10:32 am
Andrej Milas (5/18/2009)
why not just grab the TOP 2? and choose the min of the 2?
That is what Gianluca Sartori posted earlier on in the first page, I guess it is more efficent? to use CTEs and Rank(). Also as this seems like an exam question it is probably better to learn Rank() at some stage..
May 18, 2009 at 10:52 pm
Hi,
Try out this query.Using this u can find Nth highest salary.
declare @n int
set @n=2
Select * From Employee E1 Where
(@n-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
Thanks
Shailesh
May 19, 2009 at 11:38 pm
select max(salary) from employee_details e1
where 2 <=(select count(*) from employee_details e2 where e1.employeeid <= e2.employeeid)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply