Thread: oracle's first_value function for postgres?
Oracle has a very handy function called first_value, which can be used to turn a set like this: a 10 a 3 b 1 c 30 c 10 d 1 d 20 ...into this: a 10 b 1 c 30 d 1 Does postgres have something equivalent, or, even better, is there a reasonable way to express this in standard SQL?
* Ben (bench@silentmedia.com) wrote: > Oracle has a very handy function called first_value, which can be used > to turn a set like this: > > a 10 > a 3 > b 1 > c 30 > c 10 > d 1 > d 20 > > ...into this: > > a 10 > b 1 > c 30 > d 1 > > Does postgres have something equivalent, or, even better, is there a > reasonable way to express this in standard SQL? Isn't this just: distinct on (col1)? Stephen
Attachment
Sweet, indeed it is. DISTINCT ON seems to be a postgresism, but I can live with that. Thanks. Stephen Frost wrote: > >Isn't this just: distinct on (col1)? > > Stephen > >
On Tue, Sep 13, 2005 at 12:15:21PM -0700, Ben wrote: > Oracle has a very handy function called first_value, which can be used > to turn a set like this: Look at DISTINCT ON () > Does postgres have something equivalent, or, even better, is there a > reasonable way to express this in standard SQL? In standard SQL, not really, which is why it's an extention... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes: > On Tue, Sep 13, 2005 at 12:15:21PM -0700, Ben wrote: >> Oracle has a very handy function called first_value, which can be used >> to turn a set like this: > Look at DISTINCT ON () >> Does postgres have something equivalent, or, even better, is there a >> reasonable way to express this in standard SQL? > In standard SQL, not really, which is why it's an extention... Another way is to create an aggregate with a no-op transition function ... also nonstandard, but you might be able to duplicate Oracle's syntax that way. regards, tom lane
* Ben (bench@silentmedia.com) wrote: > Sweet, indeed it is. DISTINCT ON seems to be a postgresism, but I can > live with that. Uhhh, I was pretty sure it was standard SQL... I'd have to go look it up though, to be sure. Don't forget to order by to make sure you get something consistent. :) Enjoy, Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes: > * Ben (bench@silentmedia.com) wrote: >> Sweet, indeed it is. DISTINCT ON seems to be a postgresism, but I can >> live with that. > Uhhh, I was pretty sure it was standard SQL... Nope, definitely a Postgres-ism. regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost <sfrost@snowman.net> writes: > > * Ben (bench@silentmedia.com) wrote: > >> Sweet, indeed it is. DISTINCT ON seems to be a postgresism, but I can > >> live with that. > > > Uhhh, I was pretty sure it was standard SQL... > > Nope, definitely a Postgres-ism. Huh. Guess I suck then. I actually do this a fair bit, is there any way to do it in standard SQL? It's terribly useful... Thanks, Stephen
Attachment
Martijn van Oosterhout wrote: > On Tue, Sep 13, 2005 at 12:15:21PM -0700, Ben wrote: > >>Oracle has a very handy function called first_value, which can be used >>to turn a set like this: > > > Look at DISTINCT ON () > > >>Does postgres have something equivalent, or, even better, is there a >>reasonable way to express this in standard SQL? > > > In standard SQL, not really, which is why it's an extention... No? It seems this is similar SELECT a,b FROM ( SELECT a,b, RANK() OVER ( PARTITION BY a ORDER BY b ) rank FROM my_table ) WHERE rank = 1 ORDER BY a, rank DESC; which I think is standard sql-99 with the sql-99 olap extention.