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, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
