Ranking dilemma - 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)

Ranking dilemma

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-20 : 17:55:28

I want to group employees by their JOB.DEPTID and rank them by their EMPLOYMENT.HIRE_DT

JOB table has EMPLID and DEPTID
EMPLOYMENT table has EMPLID and HIRE_DT

So the derived field will rank the employees by their hire date, within their department.

Thanks in advance.

I know enough to know that I don't know enough.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-20 : 19:17:23

Can you post some sample data and an expected resultset?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-20 : 21:14:40

???


SELECT
 j.EMPLID,
 j.DEPTID,
 e.HIRE_DT
FROM
 JOB j
 INNER JOIN EMPLOYMENT e ON j.EMPLID = e.EMPLID
GROUP BY
 j.DEPTID,
 e.HIRE_DT

fyi, if this is what you need, you should probably buy a SQL Server development book. The Books links has several good books you could buy. In addition, you can read and learn alot from Books Online.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-21 : 15:36:01

Try:

SELECT
	j.DEPTID,
	j.EMPID,
	e.HIRE_DT,
	RANK AS ( 	SELECT COUNT(*)
			FROM EMPLOYMENT e2
			WHERE e.HIRE_DT > e2.HIRE_DT )
FROM
	JOB j JOIN EMPLOYMENT e ON j.EMPLID = e.EMPLID
ORDER BY
	j.DEPTID,
	e.HIRE_DT,
	j.EMPID

You realize that since the EMPLOYMENT table has no account of which department the
employee was hired into, the employee has the same HIRE_DT for all DEPTID ?

rockmoose

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-21 : 17:49:39

I figured it out!!!

The last post helped me realize the big problem I had.....I was trying to count rows based on 2 fields that weren't in the same table. So I basically created a view with the fields I needed, and then did the count of a subquery of that view.

SELECT *,

(SELECT COUNT(*)
FROM (
SELECT J.DEPTID,J.EMPLID,E.HIRE_DT
FROM DEMOV4.dbo.PS_JOB J, DEMOV4.dbo.PS_EMPLOYMENT E
WHERE J.EMPLID = E.EMPLID
AND J.EFFDT = (SELECT MAX(J2.EFFDT)
FROM DEMOV4.dbo.PS_JOB J2
WHERE J.EMPLID = J2.EMPLID)
) VW2
WHERE VW.DEPTID = VW2.DEPTID
AND VW.HIRE_DT > VW2.HIRE_DT
) + 1 AS RANK

FROM
(
SELECT J.DEPTID,J.EMPLID,E.HIRE_DT
FROM DEMOV4.dbo.PS_JOB J, DEMOV4.dbo.PS_EMPLOYMENT E
WHERE J.EMPLID = E.EMPLID
AND J.EFFDT = (SELECT MAX(J2.EFFDT)
FROM DEMOV4.dbo.PS_JOB J2
WHERE J.EMPLID = J2.EMPLID)
) VW
ORDER BY VW.DEPTID,VW.HIRE_DT

I know enough to know that I don't know enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources