急,一道sql题??
问个问题
ID NAME SCOPE
1 AAA 1
2 AAA 8
3 AAA 6
4 bbb 2
5 bbb 1
如果这张表我只想把第2行和第4行取出来,怎么写SQL语句啊?
也就是取出相同名字的scope的最大值行
希望是取出整行来,而不是这样
select max(scope) from table t group by name;
最好是select * from...怎么样的
谢谢!
[解决办法]
select ID,NAME,SCOPEFROM (NAME,SCOPE) IN (SELECT NAME,MAX(SCOPE) FROM TAB GROUP BY NAME )
[解决办法]
select * from tab a
where not exists(select 1 from tab b where a.name=b.name and a.scope<b.scope)
[解决办法]
SELECT ID, NAME, SCOPE FROM TAB WHERE (NAME, SCOPE) IN SELECT NAME, MAX(SCOPE) FROM TAB GROUP BY NAME
[解决办法]