On Wed, August 15, 2007 13:09, Kebbel, John said:
> I have a table with 1600 student locks in random order. I would like them
> permanently sorted by the locker number they are assigned to. I assumed
> that ...
> ~ I would copy the table under a different name
> ~ Delete all records from this copy
> ~ Write a statement that would copy the records from the original table
> into the copied table in SORTED order
> ~ Delete the original table
> ~ Rename the copy to the same name as the original
> Question 1: Is there a better way to get the desired result?
> Question 2: If not, what would the Insert/Select statement look like that
> would copy the records over in sorted order?
Why not just create an index on the locker number field?
Step 1 make it an index and list to endure no two students have the same
Step 2. modify the index so that its unique and the primary index.
One of the advantages of a database is you can pull the information in any
order by LockerNumber to generate a list by lockers,
order by LastName, FirstName to generate a list in alphabetic order.
Unless you specify the order the database engine is free to return them in
whatever order comes out. Even if it happened to deliver them in locker
order after the process you specified above, the first delete and/or add
would change that.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27