SQL 如何从sql查询中获取第一条和最后一条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1485391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get First and Last record from a sql query?
提问by kender
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:
我在PostgreSQL 中有一个表，我在它上面运行了一个查询，其中有几个条件返回多行，按其中一列排序。一般来说是：
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.
现在我只对从这个查询中获取第一行和最后一行感兴趣。我可以将它们放在数据库之外，在我的应用程序中（这就是我实际所做的），但想知道是否为了更好的性能，我不应该只从数据库中获取我真正感兴趣的那 2 条记录。
And if so, how do I modify my query?
如果是这样，我该如何修改我的查询？
回答by Mitch Wheat
[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)
回答by a_horse_with_no_name
You might want to try this, could potentially be faster than doing two queries:
你可能想试试这个，可能比做两个查询更快：
select <some columns>
from (
SELECT <some columns>,
row_number() over (order by date desc) as rn,
count(*) over () as total_count
FROM mytable
<maybe some joins here>
WHERE <various conditions>
) t
where rn = 1
or rn = total_count
ORDER BY date DESC
回答by Robo
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
回答by neha
last record :
最后记录：
SELECT * FROM `aboutus` order by id desc limit 1
first record :
第一条记录：
SELECT * FROM `aboutus` order by id asc limit 1
回答by Natan Medeiros
In all the exposed ways of do until now, must go through scan two times, one for the first row and one for the last row.
到目前为止，在所有暴露的方法中，都必须经过两次扫描，一次扫描第一行，一次扫描最后一行。
Using the Window Function "ROW_NUMBER() OVER (...)" plus "WITH Queries", you can scan only one time and get both items.
使用窗口函数“ROW_NUMBER() OVER (...)”加上“WITH Queries”，您只能扫描一次并获得两项。
Window Function: https://www.postgresql.org/docs/9.6/static/functions-window.html
窗口函数：https: //www.postgresql.org/docs/9.6/static/functions-window.html
WITH Queries: https://www.postgresql.org/docs/9.6/static/queries-with.html
WITH查询：https: //www.postgresql.org/docs/9.6/static/queries-with.html
Example:
例子：
WITH scan_plan AS (
SELECT
<some columns>,
ROW_NUMBER() OVER (ORDER BY date DESC) AS first_row, /*It's logical required to be the same as major query*/
ROW_NUMBER() OVER (ORDER BY date ASC) AS last_row /*It's rigth, needs to be the inverse*/
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC)
SELECT
<some columns>
FROM scan_plan
WHERE scan_plan.first_row = 1 OR scan_plan.last_row = 1;
On that way you will do relations, filtrations and data manipulation only one time.
通过这种方式，您将只进行一次关系、过滤和数据操作。
Try some EXPLAIN ANALYZE on both ways.
尝试两种方式的解释分析。
回答by sundeepkumar dv
SELECT <rows> FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME)
UNION
SELECT <rows> FROM TABLE_NAME WHERE ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME)
or
或者
SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME)
OR ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME)
回答by Ampolu Balaji
select *
from {Table_Name}
where {x_column_name}=(
select d.{x_column_name}
from (
select rownum as rno,{x_column_name}
from {Table_Name})d
where d.rno=(
select count(*)
from {Table_Name}));
回答by Sergey P. aka azure
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT ;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT ;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
Got it from here: https://wiki.postgresql.org/wiki/First/last_(aggregate)
从这里得到它：https: //wiki.postgresql.org/wiki/First/last_(aggregate)
回答by Mikhail Sotnikov
In some cases - when not so many columns - useful the WINDOW functions FIRST_VALUE() and LAST_VALUE().
在某些情况下 - 当没有那么多列时 - WINDOW 函数 FIRST_VALUE() 和 LAST_VALUE() 很有用。
SELECT
FIRST_VALUE(timestamp) over (ORDER BY timestamp ASC) as created_dt,
LAST_VALUE(timestamp) over (ORDER BY timestamp ASC) as last_update_dt,
LAST_VALUE(action) over (ORDER BY timestamp ASC) as last_action
FROM events
This query sort data only once.
此查询仅对数据排序一次。
It can be used for getting fisrt and last rows by some ID
它可用于通过某个 ID 获取第一行和最后一行
SELECT DISTINCT
order_id,
FIRST_VALUE(timestamp) over (PARTITION BY order_id ORDER BY timestamp ASC) as created_dt,
LAST_VALUE(timestamp) over (PARTITION BY order_id ORDER BY timestamp ASC) as last_update_dt,
LAST_VALUE(action) over (PARTITION BY order_id ORDER BY timestamp ASC) as last_action
FROM events as x
回答by TaTa
How to get the First and Last Record of DB in c#.
如何在c#中获取DB的第一条和最后一条记录。
SELECT TOP 1 *
FROM ViewAttendenceReport
WHERE EmployeeId = 4
AND AttendenceDate >='1/18/2020 00:00:00'
AND AttendenceDate <='1/18/2020 23:59:59'
ORDER BY Intime ASC
UNION
SELECT TOP 1 *
FROM ViewAttendenceReport
WHERE EmployeeId = 4
AND AttendenceDate >='1/18/2020 00:00:00'
AND AttendenceDate <='1/18/2020 23:59:59'
ORDER BY OutTime DESC;
