Quick Tip: Order By 1 Desc
Did you know that instead of specifying the name or column alias in a SQL Server Order By expression you can optionally use an integer representing the position of the column name or alias within the select list you want to sort by.
So both of these queries return the same result set ...
-- find me the last 10 log entries select top 10 * from adventureworks.dbo.databaselog order by databaselogid desc -- find me the last 10 log entries select top 10 * from adventureworks.dbo.databaselog order by 1 desc
In the database I am currently working with, 99% of the tables have an identity column defined as the first column in the tables schema. Using the 'order by 1 desc' is a nice portable short hand for fetching the most recent rows that were added to a table (nice for trouble-shooting data issues or combing through log and audit tables). Sometimes our column names get a little unwieldy, so this technique ends up saving me a few keystrokes.
Comments
Although thats true, I always feel it makes the SQL more readable and more explicit if you actually declare the column name.
I would stick to using column names since if a column position changes, you don't end breaking your app functionality, since it won't work the way it shoul.