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

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:

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.

And if so, how do I modify my query?

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; 

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

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

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)

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

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

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.

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

last record :

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

first record :

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

In some cases - when not so many columns - useful the WINDOW functions FIRST_VALUE() and LAST_VALUE().

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

This query sort data only once.

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

SELECT DISTINCT
    order_id,
    FIRST_VALUE(timestamp) over (PARTITION BY order_id ORDER BY timestamp ASC) as created_dt,
    LAST_VALUE(timestamp) over (PARTITION BY order_id ORDER BY timestamp ASC) as last_update_dt,
    LAST_VALUE(action) over (PARTITION BY order_id ORDER BY timestamp ASC) as last_action

FROM events as x

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

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