Mysql select row of max id is returns wrong data

问题: I have a table as follows : tabl1 : id name created_on ---- ------ ---------- 1 red 2016 2 green...

问题:

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

回答1:

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


回答2:

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;
  • 发表于 2019-01-02 19:50
  • 阅读 ( 188 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除