SQL Listing all column names alphabetically

sql sorting alphabetical
Shamim Hafiz picture Shamim Hafiz · Nov 2, 2010 · Viewed 72.8k times · Source

I know that

SELECT * FROM Table

will list all columns in the table, but I am interested in listing the columns in alphabetical order.

Say, I have three columns, "name", "age" and "sex".

I want the columns organized in the format

|age| |name| |sex|

Is it possible to do this with SQL?

Answer

paxdiablo picture paxdiablo · Nov 2, 2010

Yes, and no :-)

SQL itself doesn't care what order the columns come out in but, if you were to use:

select age, name, sex from ...

you'd find that they probably came out in that order (though I'm not sure SQL standards mandate this).

Now you may not want to do that but sometimes life isn't fair :-)

You also have the other possibility of using the DBMS data definition tables to dynamically construct a query. This is non-portable but most DBMS' supply these table (such as DB/2's SYSIBM.SYSCOLUMNS) and you can select the column names from there in an ordered fashion. Something like:

select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;

Then you use the results of that query to construct the real query:

query1 = "select column_name from sysibm.syscolumns" +
         " where owner = 'pax' and table_name = 'movies'" +
         " order by column_name"
rs = exec(query1)
query2 = "select"
sep = " "
foreach colm in rs:
    query2 += sep + colm["column_name"]
    sep = ", "
query2 += " from movies order by rating"
rs = exec(query2)
// Now you have the rs recordset with sorted columns.

However, you really should critically examine all queries that select * - in the vast majority of cases, it's unnecessary and inefficient. And presentation of the data is something that should probably be done by the presentation layer, not the DBMS itself - the DBMS should be left to return the data in as efficient a manner as possible.

Related questions

SQL how to make null values come last when sorting ascending

I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime field is …

Read More
SQL Query - Using Order By in UNION

How can one programmatically sort a union query when pulling data from two tables? For example, SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1 Throws an exception Note: this …

Read More
How to use SQL Order By statement to sort results case insensitive?

I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this: A B C T a b c g …

Read More