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 1Does 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
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/
Show quoted text
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.
Sweet, indeed it is. DISTINCT ON seems to be a postgresism, but I can
live with that.
Thanks.
Stephen Frost wrote:
Show quoted text
Isn't this just: distinct on (col1)?
Stephen
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
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
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.