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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
