Re: first_value/last_value - Mailing list pgsql-general

Here is an example that works in a single query.  Since you have two different orders you want the data back in, you need to use subqueries to get the proper data back, but it works, and is very fast.

CREATE TEMPORARY TABLE foo AS 

SELECT generate_series as bar

FROM generate_series(1, 1000000);

CREATE INDEX idx_foo_bar ON foo (bar);

SELECT *

FROM (

SELECT bar

FROM foo

ORDER BY bar asc

LIMIT 1

) x

UNION ALL 

SELECT *

FROM (

SELECT bar

FROM foo

ORDER BY bar desc

LIMIT 1

) y;

DROP TABLE foo;