You can sort on columns by their column number

Author: Steven Neiland
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

Mark Gregory's Gravatar
Mark Gregory
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.

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing