How to get First and Last record from a sql query?
SqlPostgresqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
[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)
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
last record :
SELECT * FROM `aboutus` order by id desc limit 1
first record :
SELECT * FROM `aboutus` order by id asc limit 1
Solution 5 - Sql
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.
Solution 6 - Sql
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)
Solution 7 - Sql
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
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
;
Solution 8 - Sql
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)
Solution 9 - Sql
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}));
Solution 10 - Sql
-- 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)
Solution 11 - Sql
Why not use order by asc limit 1
and the reverse, order by desc limit 1
?
Solution 12 - Sql
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
Solution 13 - Sql
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;
Solution 14 - Sql
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);
Solution 15 - Sql
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;