SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

05 November 2007


ORDER BY clause in statements with a UNION

Imagine you write a SELECT statement with a UNION or UNION ALL in it with each subselect having a GROUP BY clause, then add an ORDER BY clause to present the results set in a particular order. How do you construct the ORDER BY clause so that the data is presented in order of the group function or aggregate function?

Let me take this one step at a time.

Take a simple SELECT statement with a GROUP BY and an ORDER BY:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status

That is pretty straightforward. You could also write the ORDER BY as:
or even:
and the statement will work as expected.

Now let's add a UNION ALL:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status
SELECT b_status, COUNT(*) AS cnt_b
FROM device
GROUP BY b_status

This works fine. But if you change the ORDER BY clause in the previous statement so that it reads:
ORDER BY cnt_b
both Oracle and SQL Server will give error messages. In Oracle:
SQL Error: ORA-00904: "CNT_B": invalid identifier
In SQL Server:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'cnt_b'.
Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Now change the ORDER BY clause to read:

In SQL Server, the statement will work just fine. In Oracle the statement will give an error message.
SQL Error: ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Is this an important difference in behaviour? Almost certainly not. It has never been advisable or correct to specify an aggregate function in an ORDER BY clause. The developer should have given the aggregate function the same name in both select lists of the UNION (i.e. both COUNT(*)'s should have been called cnt). Or they should have specified the name of the select list item taken from the first part of the UNION. Or alternatively the numeric method of specifying an ORDER BY could have been used.



September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]