我有以下简单的SQL语句
SELECT id, name, value_name, value_id
FROM table
GROUP BY id
ORDER BY value_id DESC
当进行分组时,我希望获取value_id最大的元组中的value_name和value_id。目前的情况是我获取到了最小值。例如:
1, name1, valuename, 3 (where i know that there is a value_id of 5)
请问您能帮忙吗?
我有以下简单的SQL语句
SELECT id, name, value_name, value_id
FROM table
GROUP BY id
ORDER BY value_id DESC
1, name1, valuename, 3 (where i know that there is a value_id of 5)
请问您能帮忙吗?
在字段列表中将value_id
更改为MAX(value_id) AS value_id
...
实际上,现在我看看,我认为你想要的是这样的:
SELECT a.id, a.name, MAX(b.value_id) AS value_id, (SELECT b.value_name FROM table AS b WHERE b.id = a.id AND b.value_id = MAX(a.value_id)) AS value_name
FROM table AS a
GROUP BY a.id, a.name
ORDER BY value_id DESC
SELECT id, name, value_name, MAX(value_id )
FROM table
GROUP BY id, name, value_name
ORDER BY value_id DESC
select
city, length(city)
from station
group by city
having length(city)=(select min(length(city)) from station) order by city ASC limit 1;
select
city, length(city)
from station group by city having length(city)=(select max(length(city)) from station)
limit 1;
您可以通过以下语句获取最大的value_id:
SELECT id, name, value_name, value_id
FROM table
GROUP BY value_id
HAVING max(value_id)
如果您想获得其他列,可以使用子查询。