Max(created_at) showing the right column but not the rest of the data SQL-CodePudding

I want to fetch the latest entry to the database

I have this data

When I run this query

select id, parent_id, amount, max(created_at) from table group by parent_id it correctly returns the latest entry but not the rest of the column

what I want is

how do I achieve that?

Sorry that I posted image instead of table, the table won't work for some reason

CodePudding user response：

That's a good use case for a window function like RANK:

SELECT id, parent_id, amount, created_at 
FROM ( 
  SELECT id, parent_id, amount, created_at, 
    RANK() OVER (PARTITION BY parent_id ORDER BY created_at DESC) parentID_rank
    FROM yourtable) groupedData
WHERE parentID_rank = 1;

or with ORDER BY clause if necessary:

SELECT id, parent_id, amount, created_at 
FROM ( 
  SELECT id, parent_id, amount, created_at, 
    RANK() OVER (PARTITION BY parent_id ORDER BY created_at DESC) parentID_rank
    FROM yourtable) groupedData
WHERE parentID_rank = 1
ORDER BY id;

CodePudding user response：

select id, parent_id, amount, max(created_at) from table group by parent_id order by max(created_at) desc limit 1

CodePudding user response：

You can fetch the desired output using subquery. In the subquery fetch the max created_at of each parent_id which will return the row with max created_at for each parent_id. Please try the below query.

SELECT * FROM yourtable t WHERE t.created_at = 
(SELECT MAX(created_at) FROM yourtable WHERE parent_id = t.parent_id);

If the id column in your table is AUTO_INCREMENT field then you can fetch the latest entry with the help of id column too.

SELECT * FROM yourtable t WHERE t.id = 
(SELECT MAX(id) FROM yourtable WHERE parent_id = t.parent_id);