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

mysql returns wrong id with max(date)

My requirement is to get ID of a row where date_created is max for which I have written below query. But everytime I run it its returning wrong ID.

In this post select returns wrong id when using max people are saying I need to add group by but unfortunatly its still showing wrong ID even after applying group by.

Does anyone know why this is hapening?

Query written:
SELECT c.id,MAX(c.date_added)
FROM cache c
GROUP BY c.project_id

Output:
1373,2023-10-04 05:30:41

Expected Output:
1374,2023-10-04 05:30:41

https://phpout.com/wp-content/uploads/2023/10/UU1Cz.png)

Below are DB details from phpmyadmin:
Server: app_db via TCP/IP
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.2.21-MariaDB-1:10.2.21+maria~bionic-log – mariadb.org binary distribution
Protocol version: 10

Limitations
I cant use ID in max field and there could be a situation where Id could be less but its date_added is higher (https://phpout.com/wp-content/uploads/2023/10/DTZHI.png)

Tried group by (select returns wrong id when using max) – Didnt work
Tried alias – didnt work

I have created SQL Fiddle is someone wants to try it http://sqlfiddle.com/#!9/3cc9dbd/1

Tags:

3

Answers


  1. 0

    I simply selected the max date by ordering it DESC
    hope this is what u wanted

    Try this:

    SELECT id, date_added FROM cache ORDER BY date_added DESC LIMIT 1;

    I’m getting this result

    id date_added
1374 2023-10-04T05:30:41Z
    Login or Signup to reply.
  2. 0

    I think this might work for you:

    SELECT id, date_added FROM cache WHERE date_added = (SELECT MAX(date_added) FROM cache) GROUP BY project_id;
    Login or Signup to reply.
  3. 0

    I think @kyleprr has the right idea, but I would solve it with a join to add the dimension of last added id per project_id:

    SELECT
  c.project_id,
  c.id,
  c.date_added
FROM
  `cache` AS c
JOIN  
  (SELECT
      project_id,
      MAX(date_added) AS last_date_added
  FROM
      `cache`
  GROUP BY
      project_id
  ) AS lastid
  ON
      lastid.project_id = c.project_id
      AND lastid.last_date_added = c.date_added
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search