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)