SQL Server: Sorting by Column Numbers

If you've come across this post then you are likely aware that in SQL Server you can use the ORDER BY clause to sort by specific columns by name, but did you know that you can also do it by number? It's a quick and dirty way to sort without having to spell out each column name.

Here's an example of how you could sort by the LAST_NAME column:

   SELECT USER_ID,

          FIRST_NAME,

          LAST_NAME

     FROM MY_TABLE

 ORDER BY LAST_NAME

Or, you can accomplish this same thing by using '3' in the ORDER BY clause. This tells SQL Server that you want to sort by the third column (which is LAST_NAME):

   SELECT USER_ID,

          FIRST_NAME,

          LAST_NAME

     FROM MY_TABLE

 ORDER BY 3

As with everything in tech you'll find varying opinions on whether or not this is a good thing to do. I would say that it's worth using if you're just writing a quick ad-hoc query. It can save time. On the other hand, if this is something you plan on saving or reusing, I would advise entering the column name instead. This way you know it will keep sorting the way you intended even if you add other columns later on that end up changing the position of the column by which you wanted to sort.

Categories: query, sorting, SQL, SQL Server