Order By items not in the select list

Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:

> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:

>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:

> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>