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

kender_4172 0
Viewed 332041

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?

postgresql sql
15 Answers

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

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

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

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

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;
user_187983460
Related