Rank Records within a Group Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following records in MyTable (without the Rank column):

EmplID RaiseDate Rank
13 10/1/2003 1
13 12/31/2002 2
13 12/31/2001 3
51 9/15/2003 1
51 9/15/2002 2
51 1/1/2001 3
51 6/30/2000 4

What I am hoping for is some SQL text that would Rank the RaiseDate in descending order within each EmplID group. The final result should look like the table above WITH the rank column. Can anyone help?
 
You might try a query whose SQL looks something like this:

SELECT
MyTable.EmplID,
MyTable.RaiseDate,
(SELECT
Count(*)
FROM
MyTable AS Self
WHERE
Self.EmplID = MyTable.EmplID
AND
Self.RaiseDate <= MyTable.RaiseDate) AS Rank
FROM
MyTable
ORDER BY
MyTable.EmplID,
MyTable.RaiseDate

You can achieve similar results using the DCount function instead of a
scalar subquery (that is, the SELECT statement that calculates the Rank)
above.

Alternatively, you might use a totals query with a self-join, as in
something like this:

SELECT
MyTable.EmplID,
MyTable.RaiseDate,
Count(*) AS Rank
FROM
MyTable
INNER JOIN
MyTable AS Self
ON
MyTable.EmplID = Self.EmplID
WHERE
Self.RaiseDate <= MyTable.RaiseDate
GROUP BY
MyTable.EmplID,
MyTable.RaiseDate
ORDER BY
MyTable.EmplID,
MyTable.RaiseDate


Kirk P said:
I have the following records in MyTable (without the Rank column):

EmplID RaiseDate Rank
13 10/1/2003 1
13 12/31/2002 2
13 12/31/2001 3
51 9/15/2003 1
51 9/15/2002 2
51 1/1/2001 3
51 6/30/2000 4

What I am hoping for is some SQL text that would Rank the RaiseDate in
Click to expand...
descending order within each EmplID group. The final result should look
like the table above WITH the rank column. Can anyone help?
 
Back
Top