sql - 如何从sql查询中获取第一个和最后一个记录？

Question

在 PostgreSQL 中，我使用多个条件对其运行查询，返回多行，按其中一列排序。示例:

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

如何从该查询中获取第一行和最后一行？

Answer 1

[警告:可能不是最有效的方法]:

(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)