Rank Records within a Group Query

[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?

 

[Brian Camire]

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

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?