Re: SELECT DISTINCT ... ORDER BY problem

Adam Rich Wrote:
> > >
> > > When we get windowing functions, a lot of this pain will go away :)
> > >
> >
> > Yes! Hope it won't be too long now. The patch seems to behave like it
> > should
> > now :)
> > Hopefully we'll see it commited for 8.4.
> >
> > Though this does not look too much cleaner at least it's standard SQL:
> >
> > A preview for Madi:
> >
> > SELECT foo,bar
> > FROM (SELECT foo,bar,
> >              ROW_NUMBER() OVER (PARTITION BY foo ORDER BY bar) AS pos
> >       FROM table
> > ) AS t
> > WHERE pos = 1
> > ORDER BY bar;
> >
> > Probably easier to understand what's going on in this one.
> >
> > David.
> >
>
> Is Oracle's FIRST_VALUE function not a SQL standard?   The way I would
> do this in Oracle looks like:
>
> SELECT foo, FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY bar) as bar
> FROM table
>
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions05
> 9.
> htm
>

FIRST_VALUE is standard in SQL:2008. But with that query you're not reducing
the rows. You're selecting the first bar for each foo. There may be many
foo's. You could get


Foo  | bar
A    |  a
A    |  a
C    |  a
C    |  a

Where with the other query we'd see only two rows, one with foo as 'a' and
one as 'c'.

Of course then you could nest it then do DISTINCT but then it's about as
ugly as it was previously.

David.