How to get First and Last record from a sql query

Question

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  lt some columns gt   FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  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

User · Answer

How to get the First and Last Record of DB in c    SELECT TOP 1      FROM ViewAttendenceReport   WHERE EmployeeId   4     AND AttendenceDate  gt   1 18 2020 00 00 00      AND AttendenceDate  lt   1 18 2020 23 59 59   ORDER BY Intime ASC  UNION SELECT TOP 1      FROM ViewAttendenceReport   WHERE EmployeeId   4     AND AttendenceDate  gt   1 18 2020 00 00 00      AND AttendenceDate  lt   1 18 2020 23 59 59    ORDER BY OutTime DESC

User · Answer

last record    SELECT   FROM  aboutus  order by id desc limit 1   first record    SELECT   FROM  aboutus  order by id asc limit 1

User · Answer

I think this code gets the same and is easier to read    SELECT  lt some columns gt   FROM mytable  lt maybe some joins here gt  WHERE date  gt    SELECT date from mytable  OR date  lt    SELECT date from mytable

User · Answer

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

User · Answer

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

User · Answer

First record   SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date ASC LIMIT 1   Last record   SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date DESC LIMIT 1

User · Answer

You might want to try this  could potentially be faster than doing two queries   select  lt some columns gt  from       SELECT  lt some columns gt              row number   over  order by date desc  as rn             count    over    as total count     FROM mytable      lt maybe some joins here gt      WHERE  lt various conditions gt    t where rn   1    or rn   total count ORDER BY date DESC

User · Answer

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

User · Answer

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      lt some columns gt       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  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date DESC   SELECT      lt some columns gt  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

User · Answer

Caveat  Might not be the most efficient way to do it     SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date DESC LIMIT 1   UNION ALL   SELECT  lt some columns gt  FROM mytable  lt maybe some joins here gt  WHERE  lt various conditions gt  ORDER BY date ASC     LIMIT 1

User · Answer

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

User · Answer

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

User · Answer

SELECT  lt rows gt  FROM TABLE NAME WHERE ROWID  SELECT MIN ROWID  FROM TABLE NAME   UNION SELECT  lt rows gt  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

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

Examples related to sql

Examples related to postgresql