Обсуждение: 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,