Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
So I was looking back through some very old code when I happened across this sql snippet that reminded me this was possible.
Normally when you are writing sql you name the column(s) you want to order on, however it is possible (at least in SQL Server) to order on a column by its numeric column number in the query. Take for example the following query:
SELECT
firstname
,lastname
,dob
FROM
users
ORDER BY
lastname
We can rewrite this query as:
SELECT
firstname
,lastname
,dob
FROM
users
ORDER BY
2
Note that the value 2 corresponds to the 'lastname' column lastname (1 indexed).
Warning: Unless you have an extremely unique use case such as some kind of dynamically generated sql system I cannot recommend this as a good idea. It is easily broken by changes to the sql and obfuscates the intent of the order condition. However it is useful to be aware of in case you ever encounter it.
Reader Comments
Tuesday, August 18, 2020 at 9:49:19 AM Coordinated Universal Time
Recently found a scenario where I had to use this. Querying a very wide table in SSMS, checking data, doing a select like: Select ColumnOfInterest, * from myTable order by ColumnOfInterest Which does not work with that order by. SQL Server sees it as ambiguous. Had to do this: Select ColumnOfInterest, * from myTable order by 1 Of course this was just me checking some data from SSMS. Have seen it in production code on occasion, but strongly disagree with doing that, for the same reasons you bring up.