Postgresql Selecting Most Recent Entry for a Given Id

Efficient latest record query with Postgresql

If you don't want to change your data model, you can use DISTINCT ON to fetch the newest record from table "b" for each entry in "a":

SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC

If you want to avoid a "sort" in the query, adding an index like this might help you, but I am not sure:

CREATE INDEX b_id_date ON b (id, date DESC)

SELECT DISTINCT ON (b.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY b.id, b.date DESC

Alternatively, if you want to sort records from table "a" some way:

SELECT DISTINCT ON (sort_column, a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY sort_column, a.id, b.date DESC

Alternative approaches

However, all of the above queries still need to read all referenced rows from table "b", so if you have lots of data, it might still just be too slow.

You could create a new table, which only holds the newest "b" record for each a.id -- or even move those columns into the "a" table itself.

PostgreSQL Selecting Most Recent Entry for a Given ID

There's about 5 different ways to do this, but here's one:

SELECT *
FROM yourTable AS T1 
WHERE NOT EXISTS(
    SELECT *
    FROM yourTable AS T2
    WHERE T2.ID = T1.ID AND T2.Date > T1.Date
)

And here's another:

SELECT T1.*
FROM yourTable AS T1
LEFT JOIN yourTable AS T2 ON
(
    T2.ID = T1.ID 
    AND T2.Date > T1.Date
)
WHERE T2.ID IS NULL

One more:

WITH T AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn
    FROM yourTable
)
SELECT * FROM T WHERE rn = 1

Ok, i'm getting carried away, here's the last one I'll post(for now):

WITH T AS (
    SELECT ID, MAX(Date) AS latest_date
    FROM yourTable
    GROUP BY ID
)
SELECT yourTable.*
FROM yourTable
JOIN T ON T.ID = yourTable.ID AND T.latest_date = yourTable.Date

Postgresql extract last row for each id

The most efficient way is to use Postgres' distinct on operator

select distinct on (id) id, date, another_info
from the_table
order by id, date desc;

If you want a solution that works across databases (but is less efficient) you can use a window function:

select id, date, another_info
from (
  select id, date, another_info, 
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;

The solution with a window function is in most cases faster than using a sub-query.

Get last record of a table in Postgres

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

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

Selecting the most recent entry on a timestamp cell

You want the latest entry per user. For this, you need the user id in the distinct on clause, and no other column. This guarantees one row in the resultset per user.

Then, you need to put that column first in the order by clause, followed by createdOn desc. This breaks the ties and decides which row will be retained in each group:

select distinct on (u.id) u.id , e.id ,u."Name" , u.email, e.agree, e."createdOn" 
from "user" u
inner join public.entry e on u."id" = e."userId"
order by u.id, "createdOn" desc

How to edit my postgreSQL query to select the most recent row by date for several columns

The column (or set of columns) in the distinct on clause defines the groups: if you want one row per "stock", then column stock should belong to that clause.

Then, you need an order by: it starts with the distinct on column(s), followed by one or more columns that define which row should be kept in each group: this is where the "date" column goes.

Your question does not explain what are the names of these to crucial columns in the table. Assuming that we have id and date, the logic would be:

SELECT DISTINCT ON (id) ...
FROM security_stats 
WHERE ...
ORDER BY id, date desc 

---

Related Topics