Re: 7.4, 'group by' default ordering?

On Thu, Jan 08, 2004 at 13:42:33 -0600,
Mike Nolan <nolan(at)gw(dot)tssi(dot)com> wrote:
> 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?

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.

> 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).
There was also some discussion on how the order is constrained if the sort
is on multiple columns where the value of the first column is NULL, but the
values of other columns are not. I don't have the book here with me now,
but I think the result of the discussion was that within rows with a NULL
value for the first column, they should be sorted by the values in the
later columns.