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
SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC    
LIMIT 1

Answer 2

First record:

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

Last record:

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