Re: an aggregate to return max() - 1 value?

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,