CODEWITHSUNDEEP
sql-server
Bhavana
Bhavana

Reputation: 349

order by clause question

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

Upvotes: 1

Views: 675

Answers (3)

gbn
gbn

Reputation: 432672

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).

Upvotes: 3

bernd_k
bernd_k

Reputation: 11966

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.

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

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).

Upvotes: 5

Related Questions