Postgres Pro
Downloads
Home   >   mailing lists

Re: first_value/last_value - Mailing list pgsql-general

From Adam Brusselback
Subject Re: first_value/last_value
Date
Msg-id CAMjNa7eekx6sVc3f-ZCmQZnOi3aTO7g+UeaULnAvEBAUGy9ozg@mail.gmail.com
Whole thread Raw
In response to Re: first_value/last_value  (Tom Smith <tomsmith1989sk@gmail.com>)
Responses Re: first_value/last_value  (Melvin Davidson <melvin6925@gmail.com>)
List pgsql-general
Tree view
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;

pgsql-general by date:

Previous
From: Tom Smith
Date:
Subject: Re: first_value/last_value
Next
From: Melvin Davidson
Date:
Subject: Re: first_value/last_value