Question 22062 in Mysql Question posted in
The official MySQL documentation can be found here.

Max(created_at) showing the right column but not the rest of the data SQL – Mysql

I want to fetch the latest entry to the database

I have this data

enter image description here

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

enter image description here

what I want is

enter image description here

how do I achieve that?

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

Tags:

4

Answers


  1. 0 
    select id, parent_id, amount, max(created_at)
from table
group by parent_id 
order by max(created_at) desc 
limit 1
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
  3. 0

    That’s a good use case for a window function like RANK as a subquery:

    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 for the outer query 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;

    To explain the intention:

    The PARTITION BY clause groups your data by the parent_id.

    The ORDER BY clause sorts it starting with the latest date.

    The WHERE clause just takes the entry with the latest date per parent id only.

    Login or Signup to reply.
  4. 0

    The main point here is that your query is invalid. The DBMS should raise an error, but you work in a cheat mode that MySQL offers that allows you to write such queries without being warned.

    My advice: When working in MySQL make sure you have always

    SET sql_mode = 'ONLY_FULL_GROUP_BY';

    As to the query: You are using MAX. Thus you aggregate your data. In your GROUP BY clause you say you want one result row per parent_id. You select the parent_id’s maximum created_at. You also select the parent_id’s ID, the parent_id itself, and the parent_id’s amount. The parent_id’s ID??? Is there only one ID per parent_id in your table? The amount? Is there only one amount per parent_id in the table? You must tell the DBMS which ID to show and which amount. You haven’t done so, and this makes your query invalid according to standard SQL.

    You are running MySQL in cheat mode,however, and so MySQL silently applies ANY_VALUE to all non-aggregated columns. This is what your query is turned into internally:

    select
  any_value(id),
  parent_id,
  any_value(amount),
  max(created_at)
from table
group by parent_id;

    ANY_VALUE means the DBMS is free to pick the attribute from whatever row it likes; you don’t care.

    What you want instead is not to aggregate your rows, but to filter them. You want to select only those rows with the maximum created_at per parent_id.

    There exist several ways to get this result. Here are some options.

    Get the maximum created_at per parent_id. Then select the matching rows:

    select *
from table 
where (parent_id, created_at) in
(
  select parent_id, max(created_at)
  from table
  group by parent_id
);

    Select the rows for which no newer created_at exists for the parent_id:

    select *
from table t
where not exists
(
  select null
  from table newer
  where newer.parent_id = t.parent_id
  and newer.created_at > t.created_at
);

    Get the maximum created_at on-the-fly. Then compare the dates:

    select id, parent_id, amount, created_at
from
(
  select t.*, max(created_at) over (partition by parent_id) as max_created_at
  from table t
) with_max_created_at
where created_at = max_created_at;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search