How to get First and Last record from a sql query?

Question

In PostgreSQL I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. Example:

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

How would one get the first and the last row from this query?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

last record :

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

first record :

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

Answer 5

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 Function: https://www.postgresql.org/docs/9.6/static/functions-window.html

WITH Queries: 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.

Answer 6

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)

Answer 7

I know this is a 7 year old thread, but the question was nearly identical and the accepted answer was what I started this with and eventually optimized to the following, which in my case returns consistently 85ms +-5ms with <some_column> being an indexed int field.

note1: the UNION ALL example in the accepted answer works too but was less performant in my case coming in at 300ms +-20ms.

note2: the next most upvoted answer (the row counter example) also works but was the least performant in my case coming in at 800ms +-70ms.

select
  (select <some_column> from <some_table>
    order by <some_field> limit 1)        as oldest,
  (select <some_column> from <some_table> 
    order by <some_field> desc limit 1)   as newest
;

I did note that op referenced possible joins. I haven't had the need to include joins for my own purposes (just getting the current low and high IDs in fairly dynamic view) but with this model, the subqueries for oldest and newest should be able to be full fledged queries. Haven't tested, so not sure if it would work or be optimal.

I did test this model (which may also have already been suggested above) which might be a bit easier to join against, but the performance as-is was just a bit less than half of the example above, consistently returning 220ms +-10ms in my case.

select oldest.<some_field> as old, 
       newest.<some_field> as new  
from
  (select <some_column> from <some_table>
    order by <some_field> limit 1)        as oldest,
  (select <some_column> from <some_table> 
    order by <some_field> desc limit 1)   as newest
;

Answer 8

In some cases useful the WINDOW functions FIRST_VALUE() and LAST_VALUE(). The key benefit - this query is readeable, sort data only once and it is only one query for several columns.

 SELECT
    FIRST_VALUE(timestamp) over w as created_dt,
    LAST_VALUE(timestamp) over w as last_update_dt,
    LAST_VALUE(action) over w as last_action
FROM events
WINDOW w as (ORDER BY timestamp ASC)

It can be used for getting first and last rows by some ID

SELECT DISTINCT
    order_id,
    FIRST_VALUE(timestamp) over w as created_dt,
    LAST_VALUE(timestamp) over w as last_update_dt,
    LAST_VALUE(action) over w as last_action
FROM events as x
WINDOW w as (PARTITION BY order_id ORDER BY timestamp ASC)

Answer 9

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 10

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

Answer 11

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

Answer 12

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

Answer 13

How to get the First and Last Record of DB in c#.

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; 

Answer 14

I think this code gets the same and is easier to read.

SELECT <some columns> 
FROM mytable
<maybe some joins here>
WHERE date >= (SELECT date from mytable)
OR date <= (SELECT date from mytable);

Answer 15

The correct Sql listed below

SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;

SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MAX(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;