Query Guru's, Please help Sequence Numbers in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TheNovice - 22 May 2008 19:23 GMT

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,

Signature

-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.

Reply to this Message

KARL DEWEY - 22 May 2008 19:56 GMT

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;

Signature

KARL DEWEY
Build a little - Test a little

> Good morning all,
>
[quoted text clipped - 15 lines]
>
> as always any help is always appreciated,

Reply to this Message