Resequence number field by Date

I hoping someone can help. I have a table this is set up as follows. MASTERID | ROUTEID | SEQNO | DATE |.
The ROUTEID is the PK, and the MASTERID is the FK. The SEQNO should show the order the routing was entered, however, some of them have become out of sequence. I want to resequence the number using the date field. If there a way to do this?
Thanks.

Well, that's the beauty of databases. You don't need to worry about how it is stored. At the time of query you can specify how it is retrieved...

SELECT * FROM yourtable ORDER BY date

To the best of my understanding, the database is internally ordering the data by your PK. The PK exists to streamline lookups by that field, and if the data wasn't organized from that perspective, it wouldn't be the PK. IX's are probably being stored as a sorted pointer list. I believe that if you make an IX for [DATE], then using Matt's clause shouldn't have any real cost over the potentially impossible task of reindexing your PK.

The problem isn't with the PK, it's with the SEQNO. The ROUTING tbl's MASTERID is a FK to the MASTER tbl. The ROUTEID is the only unique field in the ROURING table. The SEQNO is assigned within the each group of MASTERID. I did a sort by masterid, date and time and that gives me the order I want. Now, I would like to renumber the SEQNO based within each group of MasterID. Is that possible? SEQNO is not a PK or FK. It's generated by the code of the frontend to the DB. I think I may be able to write a program to access the resequence the numbers for me, but I wanted to see if it could be done with SQL before I go to the trouble. THanks for you help.

you could probably just use a INSERT INTO newtable (column_list) SELECT columns FROM oldtable ORDER BY masterid, date and time

before you do this, create the new table and make the seqno column autoincrement and start it wherever you want it to start. then use that insert select statement. Then, delete the old table, rename the new table to the right name...