How to Use ORDER BY in Derived Tables

SQL Server 2000

I was asked today how to select distinct columns while ordering by another column that does not appear in the column output list. For example:

select distinct [name]
from master..syscolumns
order by xtype

If you try to run this code, you will get the error message:

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

One way to get around this is to place the ORDER BY in a derived table, used as the source for the DISTINCT query. eg:

select distinct [name]
from (
select [name]

from master..syscolumns order by xtype
) as dt
However you will get this error:
Server: Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

But what if you don't want to limit your results to just the X TOP rows? What if you want all the rows?

Well not only can TOP be used to limit the number of rows a query returns, it can be used to instruct (some what redundantly) that you want all the rows: TOP 100 PERCENT.

select distinct [name]
from (
select top 100 percent [name]
from master..syscolumns order by xtype
) as dt

And there you have it! Distinct results ordered by a column that is not output!

Here's the SHOWPLAN_TEXT that shows the sort takes place:

  |--Hash Match(Aggregate, HASH:([syscolumns].[name]), RESIDUAL:([syscolumns].[name]=[syscolumns].[name]))
       |--Sort(ORDER BY:([syscolumns].[xtype] ASC))
            |--Clustered Index Scan(OBJECT:([master].[dbo].[syscolumns].[syscolumns]))

  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Blogter
  • BlogMemes
  • Yahoo Buzz

How to Use ORDER BY in Derived Tables | 1 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
How to Use ORDER BY in Derived Tables
Authored by: Anonymous on Tuesday, June 19 2007 @ 10:14 PM AEST

I'm curious... is the "select distinct [name]" guaranteed to return rows of data in the same order as it's source? (Whew - I hope that makes sense)

[ # ]

Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 Copyright © 2018 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.