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 msdb.sys.columns
order by system_type_id

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 msdb.sys.columns order by system_type_id
) 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 msdb.sys.columns order by system_type_id
) as dt

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

No tags for this post.