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

Question

我在PostgreSQL中有一个表，我用几个条件运行查询，返回多个行，按其中一列排序。一般来说它是：

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

现在我只获得第一，并从该查询的最后一行感兴趣。我可以在数据库之外获得它们（这是我实际做的），但是想知道为了获得更好的性能，我不应该从数据库中只获得我实际上感兴趣的2条记录。

如果是这样，我该如何修改我的查询？

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) 

Answer 2

第一条记录：

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

最后一个记录：

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

Answer 3

SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) 
UNION 
SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME) 

或

SELECT * FROM TABLE_NAME WHERE ROWID=(SELECT MIN(ROWID) FROM TABLE_NAME) 
          OR ROWID=(SELECT MAX(ROWID) FROM TABLE_NAME) 

Answer 4

你可能想试试这个，可能比做两个查询速度快：

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 

Answer 5

最后一条记录：

SELECT * FROM `aboutus` order by id desc limit 1 

第一个记录：

SELECT * FROM `aboutus` order by id asc limit 1 

Answer 6

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})); 

Answer 7

为什么不使用order by asc极限1和反向排序通过desc限制1 ..

？

Answer 8

SELECT 
    MIN(Column), MAX(Column), UserId 
FROM 
    Table_Name 
WHERE 
    (Conditions) 
GROUP BY 
    UserId DESC 

或

SELECT   
    MAX(Column) 
FROM    
    TableName 
WHERE   
    (Filter) 

UNION ALL 

SELECT   
    MIN(Column) 
FROM    
    TableName AS Tablename1 
WHERE   
    (Filter) 
ORDER BY 
    Column 

Answer 9

-- 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 $1; 
$$; 


-- 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 $2; 
$$; 

-- And then wrap an aggregate around it 
CREATE AGGREGATE public.LAST (
     sfunc = public.last_agg, 
     basetype = anyelement, 
     stype = anyelement 
); 

得到它从这里： https://wiki.postgresql.org/wiki/First/last_(aggregate)

Answer 10

在做，直到如今，都要经过扫描两次，一个是首先暴露的方式第一行和第一行。

使用窗口函数“ROW_NUMBER（）OVER（...）”加上“WITHQUERY”，您只能扫描一次并获取这两个项目。

窗口功能： https://www.postgresql.org/docs/9.6/static/functions-window.html

使用查询

： https://www.postgresql.org/docs/9.6/static/queries-with.html

例子：

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; 

在这样你会做关系的过滤和数据操作一次。

在两种方式上尝试一下EXPLAIN ANALYZE。