postgresql - How to get First and Last record from a sql query?

Question

I have a table in PostgreSQL, I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. In general it's:

SELECT <some columns> 
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC

Now I'm only interested in getting the first and the last row from this query. I could get them outside of the db, inside my application (and this is what I actually do) but was wondering if for better performance I shouldn't get from the database only those 2 records I'm actually interested in.

And if so, how do I modify my query?

Answer 1

[Caveat: Might not be the most efficient way to do it]:

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1)

UNION ALL

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC    
LIMIT 1)