You can sort on columns by their column number

Author: Steven Neiland
Published:

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.

What Do You Think?

Reader Comments

Mark Gregory's Gravatar
Mark Gregory
Tuesday, August 18, 2020 at 9:49:19 AM EDT

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.

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.
*
*



Archives Blog Listing