Re: SELECT DISTINCT ... ORDER BY problem
От | David Rowley |
---|---|
Тема | Re: SELECT DISTINCT ... ORDER BY problem |
Дата | |
Msg-id | 1C1D284697B44EAEBA0ED03703AD86E9@amd64 обсуждение исходный текст |
Ответ на | Re: SELECT DISTINCT ... ORDER BY problem ("Adam Rich" <adam.r@sbcglobal.net>) |
Список | 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.
В списке pgsql-general по дате отправления: