Get last record of a table in Postgres

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 o开发者_Go百科f the record ?

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

you can use

SELECT timestamp, value, card 
FROM my_table 
ORDER BY timestamp DESC 
LIMIT 1

assuming you want also to sort by timestamp?

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

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 <tablename> offset ((select count(*) from <tablename>)-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.

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.

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.

To get the last row,

Get Last row in the sorted order: In case the table has a column specifying time/primary key,

1. Using LIMIT clause

SELECT * FROM USERS ORDER BY CREATED_TIME DESC LIMIT 1;

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

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

Use the following

SELECT timestamp, value, card 
FROM my_table 
ORDER BY timestamp DESC 
LIMIT 1

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.

The column name plays an important role in the descending order:

select <COLUMN_NAME1, COLUMN_NAME2> from >TABLENAME> ORDER BY <COLUMN_NAME THAT MENTIONS TIME> 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;

In Oracle SQL,

select * from (select row_number() over (order by rowid desc) rn, emp.* from emp) where rn=1;

select * from table_name LIMIT 1;