Mysql select row of max id is returns wrong data

user_6722498 0
Viewed 759

I have a table as follows :

        tabl1 :
        id      name     created_on
        ----   ------    ----------
        1       red        2016
        2       green      2017
        3       blue       2018

and query as below 

    select max(id),
           name,
           created_on
    from   tabl1

Result :

    id     name    created_on    
    ---    ----    ----------
    3      red      2016

Here, Iam getting the max id correctly but name and created_on values are not from the same row. How can I return the result like this :

    id     name    created_on    
    ---    ----    ----------
    3      blue      2018
row max mysql
2 Answers

You should use the inner join woth max id for retrive the right row

select * from tabl1 
inner join  (
    select max(id) max_id
    from   tabl1
) t on t.max_id = tabl1.id

without a proper group by clause, the result for an aggreagtion function without declareation of the columns name, not in agregation function, in group by clause is not allowed for the most recent version of mysql and the result is unpredictable for the older version

user_35223120

I might just use LIMIT here:

SELECT id, name, created_on
FROM yourTable
ORDER BY id DESC
LIMIT 1;

If you are using MySQL 8 or later, then you would have another option:

SELECT id, name, created_on
FROM
(
    SELECT id, name, created_on, ROW_NUMBER() OVER (ORDER BY id DESC) rn
    FROM yourTable
) t
WHERE rn = 1;
user_18632290
Related