急一道sql题?该如何解决_Oracle管理

急,一道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...怎么样的
谢谢!

------解决方案--------------------

SQL code

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)
------解决方案--------------------
SQL codeSELECT ID, NAME, SCOPE  FROM TAB WHERE (NAME, SCOPE) IN  SELECT NAME, MAX(SCOPE) FROM TAB GROUP BY NAME
------解决方案--------------------
探讨
SQL code
SELECT ID, NAME, SCOPE
  FROM TAB
 WHERE (NAME, SCOPE) IN  SELECT NAME, MAX(SCOPE) FROM TAB GROUP BY NAME