如何从一个sql查询获得第一个和最后一个记录?

时间:2021-06-30 08:33:16

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.

现在我只想从这个查询中获取第一行和最后一行。我可以让它们在db之外,在我的应用程序中(这是我实际做的),但是我想知道是否为了更好的性能,我不应该从数据库中得到我真正感兴趣的两个记录。

And if so, how do I modify my query?

如果是,如何修改查询?

10 个解决方案

#1


60  

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

#2


19  

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

#3


19  

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

#4


12  

last record :

最后的记录:

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

first record :

第一个记录:

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

#5


4  

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)

or

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

#6


3  

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.

使用Window函数“ROW_NUMBER() OVER(…)”加上“查询”,您可以只扫描一次,同时获得两个项目。

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

与查询: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.

试着用两种方法进行解释分析。

#7


1  

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

#8


0  

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

or

SELECT        
    MAX(Column) 
FROM            
    TableName
WHERE        
    (Filter)

UNION ALL

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

#9


0  

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

Got it from here: https://wiki.postgresql.org/wiki/First/last_(aggregate)

从这里获取:https://wiki.postgresql.org/wiki/First/last_(聚合)

#10


-1  

Why not use order by asc limit 1 and the reverse, order by desc limit 1?

为什么不使用asc极限1的订单,相反,用desc极限1的订单呢?

#1


60  

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

#2


19  

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

#3


19  

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

#4


12  

last record :

最后的记录:

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

first record :

第一个记录:

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

#5


4  

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)

or

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

#6


3  

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.

使用Window函数“ROW_NUMBER() OVER(…)”加上“查询”,您可以只扫描一次,同时获得两个项目。

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

与查询: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.

试着用两种方法进行解释分析。

#7


1  

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

#8


0  

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

or

SELECT        
    MAX(Column) 
FROM            
    TableName
WHERE        
    (Filter)

UNION ALL

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

#9


0  

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

Got it from here: https://wiki.postgresql.org/wiki/First/last_(aggregate)

从这里获取:https://wiki.postgresql.org/wiki/First/last_(聚合)

#10


-1  

Why not use order by asc limit 1 and the reverse, order by desc limit 1?

为什么不使用asc极限1的订单,相反,用desc极限1的订单呢?