On 25-Mar-10 23:14:53, Wayne Stallwood wrote:
Chris G wrote:
I know it isn't really necessary as it doesn't actually affect anything (except my sensibilities) but I'd like the ability to re-order columns in some sqlite databases.
Well, it *does* have some advantages, it would mean I can do things like "SELECT * FROM database" and get the columns displayed in the order I want rather than the order resulting from database creation followed by adding extra columns as I found I needed them.
That would be crazy as every time you wanted a different order you would have to re-order the tables and then every time you insert a row, guess what....you would have to re-order the tables again.
The correct way to do this surely is to order results on your queries not on the data itself
SELECT * FROM table ORDER BY column
of course you can also specify multiple columns so if say you wanted to surname and then date of birth then two identical surnames would be ordered with the oldest one first. it's naturally also possible to reverse the order etc
Your "ORDER BY column" specifies that the rows will be ordered according to a sort on the contents of column "column". If I understand Chris aright, he has created a database table with columns
firstname lastname DOB Street Salary Country Phone Town HouseNo ...
as a result of adding extra columns higgledy-piggledy after initially creating a 3-column table with "firstname" "lastname" "DOB" in the desired order, and he wanted the left-to-right order of the columns to be different, i.e. as really desired, not as created.
I don't know of any way to make "SELECT *" output the columns in anything other than the order in which they occur in the table. One can of course do it the hard way:
SELECT firstname lastname DOB \ HouseNo Street Town Country Phone Salary FROM ...
But I'm wondering (though I haven't been able to locate the syntax) if it's possible to define a permanent variable, say Cols, somewhere which is a list of the columns in the desired order, and then
SELECT Cols from ...
That way, when the next unmatched sock is stuffed into the drawer (i.e. a new column is added), all that is needed is to update the variable Cols.
Ted.
-------------------------------------------------------------------- E-Mail: (Ted Harding) Ted.Harding@manchester.ac.uk Fax-to-email: +44 (0)870 094 0861 Date: 25-Mar-10 Time: 23:48:23 ------------------------------ XFMail ------------------------------