If I understand you correctly you want to sequentially number the rows
returned by this query, and retain those numbers in rows returned by other
queries which are based on this query. Right?
One thing you need to take account of in numbering rows is that two rows
might have the same date. To differentiate between them the table's primary
key column can be used. The actual values in this are immaterial so an
autonumber column will work fine for this. A query to do this would be like
this:
SELECT *,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.ReleaseDate <= T1.ReleaseDate
AND ( T2.YourID <= T1.YourID
OR T2.ReleaseDate <> T1.ReleaseDate))
AS SequenceNumber
FROM YourTable AS T1
ORDER BY ReleaseDate, YourID;
where YourID is the primary key column. If the ReleaseDate column contains
distinct values then you can forget about distinguishing between rows with
the same date and the query becomes a lot simpler:
SELECT *,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.ReleaseDate <= T1.ReleaseDate)
AS SequenceNumber
FROM YourTable AS T1
ORDER BY ReleaseDate;
In each case the sequence number is computed by the subquery which counts
rows where the ReleaseDate value is less than or equal to the outer query's
current ReleaseDate value, the two instances of the table being
differentiated by the aliases T1 and T2 to allow for the correlation of the
subquery with the outer query. In the first query the count is also
restricted by the YourID value being less than or equal to the outer query's
current YourID value or the ReleaseDate value being different from the outer
query's current ReleaseDate value.
You can now use this query in any other query and order the rows differently;
the SequenceNumber values will remain the same. In fact if you order one of
the above queries itself differently the SequenceNumber values will still
reflect the order of the ReleaseDate values as the computation of the numbers
is in no way dependent on the order in which the rows are returned.
Ken Sheridan
Stafford, England
