conditional query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

conditional query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-02 : 07:28:59

vivek writes "How to find detail of empoloyees who are getting 2nd , 3rd Highest salary ?"

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-02 : 09:48:18

This has got to be a test question..since we get the same exact question over and over...

SELECT TOP 2 EmpId, Salary FROM (SELECT TOP 3 EmpId, Salary FROM table ORDER BY Salary DESC) AS xxx ORDER BY Salary ~~DESC~~

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-02 : 11:02:06

No wonder everyone keeps failing that course ;)

SELECT TOP 2 EmpId, Salary FROM (SELECT TOP 3 EmpId, Salary FROM table ORDER BY Salary desc) AS xxx ORDER BY Salary asc

but actually, given these top 6 emp/saleries, 2nd and 3rd highest salary is 90 and 95 (emp6 and emp4/5)
What do you want returned in this case?
emp1 100
emp2 100
emp3 100
emp4 95
emp5 95
emp6 90

Here is another couple ways to rank the employees by salary:


set nocount on
declare @emp table (empid int identity(1,1), salary int)
insert @emp (salary)
select 100 union all
select 100 union all
select 100 union all
select 95 union all
select 95 union all
select 90 union all
select 90 union all
select 89 union all
select 88 union all
select 88

select	a.empid
	,a.EmpRankBySal
	,a.salaryrank
	,b.salary
from	(
	select	empid
		,EmpRankBySal = (select count(*) 
				from @emp 
				where salary > a.salary) + 1

		,salaryRank = (select count(distinct salary) 
				from @emp 
				where salary > a.salary) + 1
	from	@emp a
	) a
JOIN	@emp b
		ON a.empid = b.empid
--where EmpRankBySal IN (2,3)
--Where salaryrank in (2,3)
order by salaryrank

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-02 : 12:09:51

Damn Hangover....

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 17:34:14

Talk about flogging a dead horse...

set nocount on
declare @emp table (empid int identity(1,1), salary int)
insert @emp (salary)
select 100 union all
select 100 union all
select 100 union all
select 95 union all
select 95 union all
select 90 union all
select 90 union all
select 89 union all
select 88 union all
select 88

SELECT * FROM @emp
WHERE Salary IN
(
	SELECT TOP 2 Salary FROM
	(
		SELECT DISTINCT TOP 3 Salary FROM @emp ORDER BY Salary DESC
	) AS m3
	ORDER BY Salary ASC
)

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources