如何从sql查询中获取第一条记录和最后一条记录？ [英] How to get First and Last record from a sql query?

问题描述

我在 PostgreSQL 中有一个表，我用几种条件对它运行查询，这些条件返回多行，由其中一列排序。通常是：

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?

推荐答案

[注意：可能不是最有效的方法]：

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

这篇关于如何从sql查询中获取第一条记录和最后一条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！