Question

sql如何查询某列最大值的行号

一个表有3列id列,name和num列
如何查询同一个name中num最大值对应的id?

Answer 1

select a.id from table a
left outer join
(
select name, max(num) max_num from table
group by name
) b
on a.name = b.name
and a.num = b.max_num

Answer 2

select id,name,num from table a,
(select name,max(num) as num from table group by name ) b
where a.name=b.name and a.num=b.num

Answer 3

SELECT id FROM table_name
WHERE num =
(SELECT MAX(num) FROM table_name);

Answer 4

select top 1 id from table where name='xxx' order by num desc;

Answer 5

SELECT a.* FROM [Table1] a WHERE NOT EXISTS (SELECT 1 FROM [Table1] b WHERE a.Name = b.Name AND a.Num < b.Num)