Showing the rank of records 1

[Edski]

Hi,
Table A has 2 fields: id (Long) and vDate (Date). PK is id+vDate. Looks like this:

id      vDate
 1 01/10/2006
 2 14/09/2006
 2 21/10/2006
 3 19/07/2005
 3 30/01/2006
 3 12/05/2006
 4 05/06/2006
 5 20/04/2005

I would like to get the following result using a SELECT Query:

id      vDate Seq
 1 01/10/2006   1
 2 14/09/2006   1
 2 21/10/2006   2
 3 19/07/2005   1
 3 30/01/2006   2
 3 12/05/2006   3
 4 05/06/2006   1
 5 20/04/2005   1

I tried but I just have no idea!
Thanks a million.
Edski

 

[PHV]

Something like this (SQL code) ?
SELECT A.id, A.vDate, Count(*) AS Seq
FROM tblUnknown AS A INNER JOIN tblUnknown AS B ON A.id = B.id
WHERE A.vDate >= B.vDate
GROUP BY A.id, A.vDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886