Query Guru's, Please help Sequence Numbers in Query

[TheNovice]

Good morning all,

I have a query that have records of route and stop information, I need to
put a sequence number starting from 1 to n (n is the next record, until the
end) restarting at the new route number.

Can this be done? my two keys are Route and Time, I just want it to look
like this.

SPRte SPTime SPSeq
------- -------- --------
4080 03:00:00AM 1
4080 03:20:00AM 2
4080 03:40:00AM 3
4130 02:01:00AM 1
4130 03:20:00AM 2
4130 03:40:00AM 3

as always any help is always appreciated,

 

[KARL DEWEY]

Try this --
SELECT Q.Route, Q.Time, (SELECT COUNT(*) FROM TheNovice_A Q1
WHERE Q1.[Route] = Q.[Route]
AND Q1.Time < Q.Time)+1 AS SPSeq
FROM TheNovice_A AS Q
ORDER BY Q.Route, Q.Time;