Get last record of a table in Postgres

Question

I m using Postgres and cannot manage to get the last record of my table    my query   client query  SELECT timestamp value card from my table      How can I do that knowning that timestamp is a unique identifier of the record

User · Accepted Answer

If under  last record  you mean the record which has the latest timestamp value  then try this   my query   client query     SELECT TIMESTAMP      value      card   FROM my table   ORDER BY TIMESTAMP DESC   LIMIT 1

User · Answer

The column name plays an important role in the descending order   select  lt COLUMN NAME1  COLUMN NAME2 gt  from  gt TABLENAME gt  ORDER BY  lt COLUMN NAME THAT MENTIONS TIME gt  DESC LIMIT 1    For example  The below-mentioned table user details  consists of the column name  created at  that has timestamp for the table   SELECT userid  username FROM user details ORDER BY created at DESC LIMIT 1

User · Answer

If you accept a tip  create an id in this table like serial  The default of this field will be   nextval  table name field seq   regclass     So  you use a query to call the last register  Using your example   pg query  connection   SELECT currval  table name field seq   AS id    I hope this tip helps you

User · Answer

Use the following    SELECT timestamp  value  card  FROM my table  ORDER BY timestamp DESC  LIMIT 1

User · Answer

These are all good answers but if you want an aggregate function to do this to grab the last row in the result set generated by an arbitrary query  there s a standard way to do this  taken from the Postgres wiki  but should work in anything conforming reasonably to the SQL standard as of a decade or more ago    -- 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      It s usually preferable to do select     limit 1 if you have a reasonable ordering  but this is useful if you need to do this within an aggregate and would prefer to avoid a subquery   See also this question for a case where this is the natural answer

User · Answer

you can use  SELECT timestamp  value  card  FROM my table  ORDER BY timestamp DESC  LIMIT 1   assuming you want also to sort by timestamp

User · Answer

Easy way  ORDER BY in conjunction with LIMIT  SELECT timestamp  value  card FROM my table ORDER BY timestamp DESC LIMIT 1    However  LIMIT is not standard and as stated by Wikipedia  The SQL standard s core functionality does not explicitly define a default sort order for Nulls   Finally  only one row is returned when several records share the maximum timestamp   Relational way   The typical way of doing this is to check that no row has a higher timestamp than any row we retrieve   SELECT timestamp  value  card FROM my table t1 WHERE NOT EXISTS     SELECT     FROM my table t2   WHERE t2 timestamp  gt  t1 timestamp      It is my favorite solution  and the one I tend to use  The drawback is that our intent is not immediately clear when having a glimpse on this query   Instructive way  MAX  To circumvent this  one can use MAX in the subquery instead of the correlation   SELECT timestamp  value  card FROM my table WHERE timestamp       SELECT MAX timestamp    FROM my table      But without an index  two passes on the data will be necessary whereas the previous query can find the solution with only one scan  That said  we should not take performances into consideration when designing queries unless necessary  as we can expect optimizers to improve over time  However this particular kind of query is quite used   Show off way  Windowing functions  I don t recommend doing this  but maybe you can make a good impression on your boss or something  -   SELECT DISTINCT   first value timestamp  OVER w    first value value  OVER w    first value card  OVER w FROM my table WINDOW w AS  ORDER BY timestamp DESC     Actually this has the virtue of showing that a simple query can be expressed in a wide variety of ways  there are several others I can think of   and that picking one or the other form should be done according to several criteria such as    portability  Relational Instructive ways  efficiency  Relational way  expressiveness  Easy Instructive way

User · Answer

To get the last row  Get Last row in the sorted order  In case the table has a column specifying time primary key   Using LIMIT clause  SELECT   FROM USERS ORDER BY CREATED TIME DESC LIMIT 1   Using FETCH clause - Reference  SELECT   FROM USERS ORDER BY CREATED TIME FETCH FIRST ROW ONLY  Get Last row in the rows insertion order  In case the table has no columns specifying time any unique identifiers  Using CTID system column  where ctid represents the physical location of the row in a table - Reference SELECT   FROM USERS WHERE CTID    SELECT MAX CTID  FROM USERS     Consider the following table  userid  username      createdtime        1         A    1535012279455        2         B    1535042279423     as per created time  this is the last row      3         C    1535012279443        4         D    1535012212311        5         E    1535012254634     as per insertion order  this is the last row  The query 1 and 2 returns  userid  username      createdtime        2         B    1535042279423    while 3 returns  userid  username      createdtime        5         E    1535012254634    Note   On updating an old row  it removes the old row and updates the data and inserts as a new row in the table  So using the following query returns the tuple on which the data modification is done at the latest  Now updating a row  using UPDATE USERS SET USERNAME    Z  WHERE USERID  3  the table becomes as  userid  username      createdtime        1         A    1535012279455        2         B    1535042279423        4         D    1535012212311        5         E    1535012254634        3         Z    1535012279443    Now the query 3 returns  userid  username      createdtime        3         Z    1535012279443

User · Answer

The last inserted record can be queried using this assuming you have the  id  as the primary key   SELECT timestamp value card FROM my table WHERE id  select max id  from my table    Assuming every new row inserted will use the highest integer value for the table s id

User · Answer

In Oracle SQL  select   from  select row number   over  order by rowid desc  rn  emp   from emp  where rn 1

User · Answer

If your table has no Id such as integer auto-increment  and no timestamp  you can still get the last row of a table with the following query   select   from  lt tablename gt  offset   select count    from  lt tablename gt  -1    For example  that could allow you to search through an updated flat file  find confirm where the previous version ended  and copy the remaining lines to your table

[sql] Get last record of a table in Postgres

Examples related to sql

Examples related to postgresql