please help :(( - 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 2005 Forums

Transact-SQL (2005)

please help :((

Author

Topic

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 11:37:19


empno ename job         mgr     hiredate                salary dept    commission
7369	Smith	Clerk	7902	1980-12-17 00:00:00.000	800.00	20	0.00
7499	Alen	Salesman	7698	1981-02-20 00:00:00.000	1600.00	30	30.00
7521	Ward	Salesman	7698	1981-02-22 00:00:00.000	1250.00	30	500.00
7566	John	Manager	7839	1981-04-02 00:00:00.000	2975.00	20	0.00
7654	Martin	Salesman	7698	1981-09-28 00:00:00.000	1250.00	30	1400.00
7698	Blake	Manager	7839	1981-05-01 00:00:00.000	2850.00	30	0.00
7782	Clark	Manager	7839	1981-06-09 00:00:00.000	2450.00	10	0.00
7788	Scott	Analyst	7566	1987-04-19 00:00:00.000	3000.00	20	0.00
7839	King	President		1981-11-17 00:00:00.000	5000.00	10	0.00
7844	Terner	Salesman	7698	1981-09-08 00:00:00.000	1500.00	30	0.00
7876	Adams	Clerk	7788	1981-05-23 00:00:00.000	1100.00	20	0.00
7900	James	Clerk	7698	1981-12-03 00:00:00.000	950.00	30	0.00
7902	Fard	Analyst	7566	1981-12-03 00:00:00.000	3000.00	20	0.00
7934	Miller	Clerk	7782	1982-01-23 00:00:00.000	1300.00	10	0.00

this is the database ... i want to select the oldest employees from each work group.

the querry i written was

select job as [Job],ename as [Oldest Employee]
from emp
where hiredate in (select min(hiredate) from emp group by job)
order by job;

and the result is

Analyst Fard
Clerk James
Clerk Smith
Manager John
President King
Salesman Alen

the problem is that the clerk appears two time becoz james hiredate and fards hiredate are same.

how can i get rid of that james other than using this querry

select job as [Job],ename as [Oldest Employee]
from emp
where hiredate in (select min(hiredate) from emp group by job) and ename <> 'james'
order by job;

please help anyone ...

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-21 : 11:45:01

[code]select e.job as [Job], e.ename as [Oldest Employee]
from emp e
join (select job, min(hiredate) as hiredate from emp group by job) m
on e.job = m.job and e.hiredate = m.hiredate
order by e.job;[/code]

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 11:46:38

thanks a lot it worked .... thanks for the fast reply

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-21 : 11:48:25

np, and you understand the difference in this and yours right?

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 12:04:15

yup .... thanks a lot ... hey 1 more doubt could u suggest me a better querry for finding the employee whos getting 4th largest salary the querry i wrote is

select top 1 ename as [Employee Having 4 th largest salary],sal as Salary
from emp where sal in (select top 4 sal from emp order by sal desc)
order by sal asc;

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-21 : 12:21:20

what if the top 5 highly paid employee's have the same salary? what do you expect in the answer?

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 12:37:00

this is the result

John 2975.00

the problem is to find the employee having 4 th largest salary

King 5000.00
Fard 3000.00
Scott 3000.00
John 2975.00
Blake 2850.00
Clark 2450.00
Alen 1600.00
Terner 1500.00
Miller 1300.00
Ward 1250.00
Martin 1250.00
Adams 1100.00
James 950.00
Smith 800.00

here john is having the 4th largest salary ... i want to knw is there a better way 2 find it than through my querry

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-21 : 13:09:17

[code]Select name,Salary
from
(Select name,Salary,ROW_NUMBER() Over (Partition by name order by salary desc)as ROWID
from Table)Z
Where Z.ROWID = 4[/code]

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 13:37:59

Select ename,sal
from
(Select ename,sal,ROW_NUMBER() Over (Partition by ename order by sal desc)as ROWID
from emp)Z
Where Z.ROWID = 4;

this doesnt return anything .. could u please explain whatz dis..

when the inner querry is executed the rowid is always 1

here is the result for inner querry

Select ename,sal,ROW_NUMBER() Over (Partition by ename order by sal desc)as ROWID
from emp;


ename   sal     rowid
Adams	1100.00	1
Alen	1600.00	1
Blake	2850.00	1
Clark	2450.00	1
Fard	3000.00	1
James	950.00	1
John	2975.00	1
King	5000.00	1
Martin	1250.00	1
Miller	1300.00	1
Scott	3000.00	1
Smith	800.00	1
Terner	1500.00	1
Ward	1250.00	1

whats the problem ?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-21 : 13:57:25

quote:
Originally posted by hardys

this is the result

John 2975.00

the problem is to find the employee having 4 th largest salary

King 5000.00
Fard 3000.00
Scott 3000.00
John 2975.00
Blake 2850.00
Clark 2450.00
Alen 1600.00
Terner 1500.00
Miller 1300.00
Ward 1250.00
Martin 1250.00
Adams 1100.00
James 950.00
Smith 800.00

here john is having the 4th largest salary ... i want to knw is there a better way 2 find it than through my querry

you didn't answer my question. what should be the answer here??

KingA 5000.00
KingB 5000.00
KingC 5000.00
KingD 5000.00
KingE 5000.00
Fard 3000.00
Scott 3000.00
John 2975.00
Blake 2850.00
Clark 2450.00
Alen 1600.00
Terner 1500.00
Miller 1300.00
Ward 1250.00
Martin 1250.00
Adams 1100.00
James 950.00
Smith 800.00

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 14:34:24

the result must be john 2975.00

my querry just returns kingd ...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-21 : 14:36:28

You need this...

Select name,Salary 
from
(Select name,Salary,ROW_NUMBER() Over (order by salary desc)as ROWID
from Table)Z
Where Z.ROWID = 4

but as Rohit said, it might not give you expected results if people have the same salaries. You need to tell us if thats the case.

EDIT: Just saw your reply...
you need this I think..

Select name,Salary 
from
(Select name,Salary,DENSE_RANK() Over (order by salary desc)as ROWID
from Table)Z
Where Z.ROWID = 4

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-21 : 14:41:42

See my edited post...you need this

Select name,Salary 
from
(Select name,Salary,DENSE_RANK() Over (order by salary desc)as ROWID
from Table)Z
Where Z.ROWID = 4

hardys
Starting Member

8 Posts

Posted - 2009-08-21 : 14:43:42

yup its workin ... could u please explain it ... thanks a lot

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-21 : 14:45:39

Check this..

http://blogs.msdn.com/craigfr/archive/2008/03/31/ranking-functions-rank-dense-rank-and-ntile.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources