Re-ordering rows in a table.

[verynewbie]

How would I re-order rows in an exisiting table where there are data already tied to it? For instance, I would like to display row ID8(Choose one) before rowID 1(Select)?

Here is an example of a table:

ID NameE
1 Select
2 General Management, Operations and Administration
3 HR/Recruitment RH
4 Finance/Accounting
5 Research and Development
6 Marketing/Sales/Communications/Customer Service
7 Legal / Intellectual Property
8 Choose one

 

[ousoonerjoe]

renumber ID 8 to 0 and do an ORDER BY ID.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[JoeAtWork]

Or if you can't change ID, add a SortBy field, number this field according to the order you want, and then in your SELECT statement do "ORDER BY SortBy".

Tip - don't number the SortBy consecutively, leave some space in between, e.g. 10, 20, 30 etc., instead of 1, 2, 3..

That makes it easier to insert a new record in between existing ones, e.g. if you want something between the second and third record, you could make SortBy=25.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[jbenson001]

Not a good idea to renumber the IDs if there is already data tied to it. You will have to make sure any related rows are updated as well. If you can do this, then you have bad database design and should have foreign keys on any related data.

 

[r937]

display row ID8(Choose one) before rowID 1(Select)?

ORDER
    BY CASE WHEN ID = 8 THEN 0 ELSE 937 END
     , ID

r937.com | rudy.ca