Re: [GENERAL] oracle's first_value function for postgres?
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] oracle's first_value function for postgres?
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Ben ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
[GENERAL] 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? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] oracle's first_value function for postgres?
* Ben ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [GENERAL] oracle's first_value function for postgres?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] oracle's first_value function for postgres?
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. pgpbhkFn4p5KA.pgp Description: PGP signature
Re: [GENERAL] oracle's first_value function for postgres?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] oracle's first_value function for postgres?
* Ben ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [GENERAL] oracle's first_value function for postgres?
Stephen Frost [EMAIL PROTECTED] writes: * Ben ([EMAIL PROTECTED]) 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
