Thread: an aggregate to return max() - 1 value?
Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks,
'select max(col) where col < max(col)' should work but you have to do 'where col < (select max(col) ' On 03/04/2010 01:09 PM, Louis-David Mitterrand wrote: > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, >
Louis-David Mitterrand wrote: > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > Mmmm what about select max(col) from table where col not in (select max(col) from table; ? Looks like a double table reading, but it works. Gerardo
SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1 In 8.4 OLAP window functions provide more standard and flexibility method but in this case it wouldn't perform as well: postgres=# select i from (select i, rank() over (order by i desc) as r from i) as x where r = 2;i ----99 (1 row) postgres=# select i from (select i, dense_rank() over (order by i desc) as r from i) as x where r = 2;i ----99 (1 row) -- greg
Hello select min(x) from (select x from data order by x desc limit 2) s; Pavel 2010/3/4 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>: > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On Thu, Mar 04, 2010 at 08:53:10PM +0000, Greg Stark wrote: > SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1 > > In 8.4 OLAP window functions provide more standard and flexibility > method but in this case it wouldn't perform as well: > > postgres=# select i from (select i, rank() over (order by i desc) as r > from i) as x where r = 2; > i > ---- > 99 > (1 row) > > postgres=# select i from (select i, dense_rank() over (order by i > desc) as r from i) as x where r = 2; > i > ---- > 99 > (1 row) Wow, I didn't know about window functions until now. It's exactly what I need. Thanks Greg, and also thanks to others who sent their suggestion. Cheers,