问题:
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;