Re: 7.4, 'group by' default ordering?

After takin a swig o' Arrakan spice grog, nolan(at)gw(dot)tssi(dot)com (Mike Nolan) belched out:
> I notice that 7.4 doesn't do default ordering on a 'group by', so you have
> to throw in an 'order by' clause to get the output in ascending group order.
>
> Is this something that most RDB's have historically done (including PG prior
> to 7.4) but isn't really part of the SQL standard?

No, in the absence of an "ORDER BY" clause to impose order, there is
no "standard" reason to expect _any_ SQL database to return results in
_any_ particular sort of order.

It would be perfectly legitimate for a database to store all data in
hash tables, and to return rows in the resulting random order, sorting
the result set only if the query specified an order.

> On a mostly unrelated topic, does the SQL standard indicate whether
> NULL should sort to the front or the back? Is there a way to force
> it to one or the other independent of whether the order by clause
> uses ascending or descending order?

NULL isn't equal to any other value, so that, heading back to that
"any order is reasonable" notion, it might, in theory, be
"standards-conformant" to randomly intersperse the NULL values amongst
the other entries that _ARE_ returned in order.

I understand that Oracle declines to include NULL entries in indices,
which doubtless has interesting implications...

Clustering NULLs together at either the start or end of a query seems
sensible; different SQL databases handle this differently...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www.ntlug.org/~cbbrowne/
It's a little known fact that the Dark Ages were caused by the Y1K
problem.