Ranking... One More Time Please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

QVCJDN - 31 Mar 2006 19:36 GMT

There are several posts that have discussed ranking within a query, but I'm
not too sharp at SQL and I'm having a difficult time. My query is below. I'm
trying to create a table that would show a ranking for employees by company
seniority date, without displaying the actual date.

SELECT [PeopleSoft Six-Digit].Name, [PeopleSoft Six-Digit].[6 Digit ADP],
[PeopleSoft Six-Digit].[Company Seniority Date], [PeopleSoft Six-Digit].[FLSA
Stat]
FROM [PeopleSoft Six-Digit]
WHERE ((([PeopleSoft Six-Digit].[FLSA Stat])<>"Exempt"))
ORDER BY [PeopleSoft Six-Digit].[Company Seniority Date];

Thanks for any help.

Reply to this Message

Tom Ellison - 31 Mar 2006 20:23 GMT

Dear QVC:

How about:

SELECT [Name], [6 Digit ADP], [FLSA Stat]
(SELECT COUNT(*)
FROM [PeopleSoft Six-Digit] T1
WHERE T1.[FLSA Stat] <> "Exempt"
AND T1.[Company Seniority Date] < T.[Company Seniority Date]) + 1
AS Rank
FROM [PeopleSoft Six-Digit] T
WHERE [PeopleSoft Six-Digit].[FLSA Stat] <> "Exempt"
ORDER BY [Company Seniority Date]

You can remove the + 1 for a 0 based ranking.

If two people have the same Seniority Date then they will have the same
rank, and the next ranking value will be skipped.

Tom Ellison

> There are several posts that have discussed ranking within a query, but
> I'm
[quoted text clipped - 13 lines]
>
> Thanks for any help.

Reply to this Message