select name, max(age) from users group by name or select a.Id, a.name , a.age , a.others
from users a , (select name, max(age) from users group by name ) b
where a.name = b.name and a.age = b.age
from users a , (select name, max(age) from users group by name ) b
where a.name = b.name and a.age = b.age
第二个,name和age联合也不一定唯一啊,ID是主键来得
select id,name, max(age),others
from users
group by id,name,others
ID | name| age | others.....
name有重复项,Id为主键,age为number,others为说明字段
求一查询
1)Name不能重复,
2)如果Name有重复去年龄最大的一条数据
3)要求能查到,包括ID和others其他字段
select (select id from user where name=a.name and age=max(a.age)) as id,name
,max(age) as age,
(select others from user where name=a.name and age=max(a.age)) as others
from user a group by name;
--的,但結果集的排序剛好相反:
select a.ID,a.name,a.age,a.others
from users a JOIN (select name,max(age) from users group by name ) b
ON a.name = b.name and a.age = b.age;
--上面語句實際上和一樓給的第二條語句是一樣的意思,呵呵!
-- 一、
select (select max(id) from user where name=a.name and age=max(a.age)) as id,name
,max(age) as age,
(select max(others) from user where name=a.name and age=max(a.age)) as others
from user a group by name;
-- 二、
select max(a.ID) ID,a.name,a.age,Max(a.others) others
from users a JOIN (select name,max(age) from users group by name ) b
ON a.name = b.name and a.age = b.age;
group by a.name,a.age;