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.

kick it on DotNetKicks.com


TrackBack

TrackBack URL for this entry:
http://mattberseth.com/blog-mt/mt-tb.fcgi/25

Listed below are links to weblogs that reference Quick Tip: Order By 1 Desc:

» Quick Tip: Order By 1 Desc from DotNetKicks.com
You've been kicked (a good thing) - Trackback from DotNetKicks.com [Read More]

Comments


Posted by: G-moon on November 15, 2007 12:00 AM

Although thats true, I always feel it makes the SQL more readable and more explicit if you actually declare the column name.

Posted by: Rakesh S on September 23, 2008 10:55 AM

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.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Consulting Services

Yep - I also offer consulting services. And heck, I'll do just about anything. If you enjoy my blog just drop me an email describing the work you need done.

Recent Comments

  • Rakesh S wrote: I would stick to using column names since if a column position changes, you don't end breaking your ...
  • G-moon wrote: Although thats true, I always feel it makes the SQL more readable and more explicit if you actually ...