sqlpostgresql

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

In PostgreSQL I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. Example:

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

How would one get the first and the last row from this query?

Solution

  • [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)