Re: 7.4, 'group by' default ordering?

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> On Thu, Jan 08, 2004 at 13:42:33 -0600,
>> 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?

> That is because group by is often done with a sort, so rows would naturally
> be in that order. If there isn't an order by clause, the set of return
> rows can be in any order.

PG has historically implemented GROUP BY with sort + uniq (and still may
if the planner thinks it better than a hash method), but I am not sure
that this is particularly widespread among other DBMSes. In any case,
the spec certainly says that you cannot expect any particular result
ordering if you didn't say ORDER BY.

>> 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?

> In SQL for Smarties, Joe Ceclko says that either NULLs should all be first
> or all be last (independent of whether the sort is ascending or descending).

If Celko really says that, I think he's wrong. SQL92 13.1 general rule
3 says:

Whether a sort key value that is null is considered greater
or less than a non-null value is implementation-defined, but
all sort key values that are null shall either be considered
greater than all non-null values or be considered less than
all non-null values.

Since they use the phraseology "greater than" and "less than", I'd
expect that switching between ASC and DESC order would reverse the
output ordering, just as it would for two ordinary values one of which
is greater than the other.

We actually went to some trouble to make this happen, a release or three
back. IIRC, at one time PG did sort NULLs to the end regardless of
ASC/DESC, but we were persuaded that this was contrary to spec.

regards, tom lane