这样的oracle 语句怎么写？

[tyrbl]

比如一张表里有学号(id),姓名(name)这两个字段,姓名有重复,我要查询出所有学生的信息,但是姓名重复的只显示一条,请问这样的语句sql语句怎样写?

[lu_huanling]

SELECT ID ,NAME FROM 表名
GROUP BY NAME
ORDER BY ID

[fxianxian]

用分析函數ROW_NUMBER()OVER(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME)
然后取第一條

SELECT ID,NAME FROM
(SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID,NAME) RN
FROM TABLE)
WHERE RN=1

[chensi05]

SELECT NAME,MAX(ID)
FROM TABLE

[oraclelogan]

[Quote=引用 1 楼 fxianxian 的回复:]
用分析函數ROW_NUMBER()OVER(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME)
然后取第一條

SELECT ID,NAME FROM
(SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID,NAME) RN
FROM TABLE)
WHERE RN=1

[/Quote]

正解啊！

[oraclelogan]

有点疑问：
人那么多，重名的可能性太大啦 ，我觉得应该让学号id成为主键，而不是姓名吧？

[ws010]

select distinct name,id from table;

[kokobox]

[Quote=引用 1 楼 fxianxian 的回复:]
用分析函數ROW_NUMBER()OVER(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME)
然后取第一條

SELECT ID,NAME FROM
(SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY ID,NAME) RN
FROM TABLE)
WHERE RN=1

[/Quote]

最好用ROW_NUMBER()OVER(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME)

正解