Re: select random order by random

On 11/1/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Thu, Nov 01, 2007 at 02:22:58PM -0400, Tom Lane wrote:
> > > SELECT random()        FROM generate_series(1, 10) ORDER BY random();
> > > SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo;
> >
> > (BTW, this is not the planner's fault; the collapsing of the two
> > targetlist entries into one happens in the parser.)
>
> Something twigged telling me that in fact the latter expression is not
> in standard SQL but a (very common) extension. A <sort key> is clearly
> indicated to be a <value expression> with no indication anywhere that
> column aliases are allowed here (though that may be in the common rules
> somewhere).

Well, the standard way I know if is to use column numbers.  i.e.:

select random() from generate_series(1,10) order by 1

That I'm pretty sure IS in the standard.  Don't see why column aliases
would be disallowed.  It's not like the where clause where the select
field doesn't exist when it fires.  The select field list does exist
when order by fires, so referring to it makes sense.