大家帮着看看，表中重复字段查询显示问题？

我有一个这样的表：
create table per_all_people_f(person_id number,last_name varchar2(20),effective_end_date date)

insert into per_all_people_f values(1,'张三',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(2,'李四',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(3,'王二',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(4,'张三',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(5,'刘五',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(6,'王二',to_date('2009-10-01','yyyy-mm-dd'));
因为last_name字段有重复的，我想查询显示出唯一last_name的数据，结果显示成这样：
1        2        李四        2009-10-1
2        4        张三        2009-10-1
3        5        刘五        2009-10-1
4        6        王二        2009-10-1

我的处理办法是：
select max(person_id) as person_id,last_name,max(effective_end_date) as effective_end_date
from per_all_people_f
where last_name in (
select last_name
from (
select person_id, last_name,max(effective_end_date) as effective_end_date
from   per_all_people_f  group by person_id,last_name
)
group by last_name
having count(*)=1)
group by last_name
union
select max(person_id) as person_id,last_name,max(effective_end_date) as effective_end_date
from per_all_people_f
where last_name in (
select last_name
from (
select person_id, last_name,max(effective_end_date) as effective_end_date
from   per_all_people_f  group by person_id,last_name
)
group by last_name
having count(*)>1)
group by last_name

这样来写的，但不知道还有别的方法吗，谢谢！

经过朋友key_feng是这样解决的：
select person_id, last_name, effective_end_date from (
select p.*, row_number() over (partition by last_name order by person_id desc) RECNO
from
per_all_people_f p
) where RECNO = 1

看来我的SQL语句还仅局限于简单的水平中，有待提高。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67668/viewspace-231278/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67668/viewspace-231278/