List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:August 15 2007 8:19pm
Subject:Re: Recreate Table With Sorted Data
View as plain text  
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
locker.
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.

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
http://www.csz.com
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061

Thread
Recreate Table With Sorted DataJohn Kebbel15 Aug
  • Re: Recreate Table With Sorted DataWm Mussatto15 Aug
  • Re: Recreate Table With Sorted DataMartijn Tonies15 Aug
    • Re: Recreate Table With Sorted DataDan Nelson15 Aug
    • Re: Recreate Table With Sorted DataMichael Dykman15 Aug
  • Re: Recreate Table With Sorted DataMartijn Tonies15 Aug
Re: Recreate Table With Sorted DataJohn Kebbel15 Aug
  • Re: Recreate Table With Sorted DataMartijn Tonies16 Aug
  • Re: Recreate Table With Sorted DataChris Boget16 Aug
Re: Recreate Table With Sorted DataJohn Kebbel18 Aug