求一sql 语句: 重复记录中id 最大的记录

[yiyi0518]

一个数据表中存在多条重复记录, id 为主键,
id number(11) prmiary key,
name varchar(10),
.........




name 可能重复,但是我需要得到相同name 的id 最大的记录

求各位大大帮忙~~

多谢了~~

[ptpa]

select * from tablename where id=（select max(id) from tablename where name=(select name from tablename group by name having(count(name))>1)）

name from tablename group by name having(count(name))>1应该先找出来name有重复的字段吧

[yiyi0518]

多谢楼上各位热心解答,
不过,我没把问题描述清楚

其实,我需要查询的记录中还有不重复的记录值,对于有重复大记录才取ID最大的记录.

不好意思不好意思, 麻烦各位了~~~~ ^_^

[xiaoxiao1984]

select name, id from
(select name ,rank () over(partition by name order by id desc) as rid , id ,
count(*) over(partition by name) as scount
from t4) where rid = 1 and scount >= 2;

[shark2004]

select * from tablename where id=select max(id) from tablename where name='名字'

[shark2004]

不好意思，忘了加括号了

select * from tablename where id=（select max(id) from tablename where name='名字'）

[xiaoxiao1984]

SQL> select * from t4;

ID NAME
---------- ----------
1 test1
2 test2
3 test3
4 test4
5 test0
6 test1
7 test2
8 test3
9 test4
10 test0
11 test1

ID NAME
---------- ----------
12 test2
13 test3
14 test4
15 test0

已选择15行。

SQL> select name, id from
2 (select name ,rank () over(partition by name order by id desc) as rid , id
from t4) where rid = 1;

NAME ID
---------- ----------
test0 15
test1 11
test2 12
test3 13
test4 14