Re: SELECT DISTINCT ... ORDER BY problem

From: "David Rowley" <dgrowley(at)gmail(dot)com>
To: "'Adam Rich'" <adam(dot)r(at)sbcglobal(dot)net>, "'David Fetter'" <david(at)fetter(dot)org>
Cc: "'Madison Kelly'" <linux(at)alteeve(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: SELECT DISTINCT ... ORDER BY problem
Date: 2008-12-09 08:13:23
Message-ID: 1C1D284697B44EAEBA0ED03703AD86E9@amd64
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Liraz Siri 2008-12-09 08:14:49 adding postgis support to turnkey postgresql
Previous Message Greg Smith 2008-12-09 04:04:28 Re: Ubuntu for servers (was TurnKey PostgreSQL)