order by clause question

Question

Can we sort on a column which is not present in the SELECT clause? Why?

Do not SHOUT - please! Welcome to SO, but we do not like being shouted at. I'll fix your question that much at least, but for future reference - all caps will get complaints every time. — Jonathan Leffler, Jan 16 '11 at 14:46
What Jonathan means is that do write in all caps. In internet world it is considered shouting. Been in a situation myself :) — Tasawer Khan, Jan 16 '11 at 14:54
[SO 2590621](http://stackoverflow.com/questions/2590621/how-to-select-distinct-rows-without-having-the-order-by-field-selected) is somewhat related. So, too, is [SO 265628](http://stackoverflow.com/questions/265628/order-by-items-must-appear-in-the-select-list-if-select-distinct-is-specified). — Jonathan Leffler, Jan 16 '11 at 15:38

score 5 · Answer 1 · answered Jan 16 '11 at 14:51

It depends on the DBMS (and often the version of the DBMS - older versions being more rigid than newer versions).

These days, most DBMS do in fact allow you to sort on a column that is not listed in the select-list. However, the earlier SQL standards certainly required the columns in the ORDER BY clause to appear in the select-list too.

The DBMS handles it by adding the non-selected column to the result set, sorting, and then projecting away (dropping) the non-selected column.

Note that using the technique means that the data order conveys information that is not available by simply looking at the data - what is called essential ordering. This is not usually a very good idea (but it is popular enough that most DBMS allow you to do it).

score 3 · Answer 2 · edited Jan 16 '11 at 16:31

The ORDER BY will refer to the table(s) in the FROM clause, unless the semantics of the sort are modified by DISTINCT or GROUP BY.

This is OK because information from mytable1 is available:

SELECT col1, col3, FROM mytable1 ORDER BY col2

This fails becase col2 has no meaning after the DISTINCT:

SELECT DISTINCT col1, col3, FROM mytable1 ORDER BY col2

When col1, col3 is collapsed by DISTINCT you lose values of col2. The same applies to a GROUP BY (DISTINCT is effectively a simple GROUP BY).

score 0 · Answer 3 · answered Jan 16 '11 at 14:57

0

When the DBMS is SQL-Server the answer is yes. But I noted that the tag was edited by Jonathan and perhaps this answer doesn't apply to your system.

answered Jan 16 '11 at 14:57

bernd_k

11,558
7
45
64

I didn't change the tags (it was [skaffman](http://stackoverflow.com/users/21234/skaffman) who did) - I just turned the volume down on the question text. – Jonathan Leffler Jan 16 '11 at 15:16
@Jonathan Sorry my fault. Thanks for the note. – bernd_k Jan 16 '11 at 15:20

order by clause question

3 Answers3