我要从一个表中查询出该表某些记录,要求标识字段不重复,且得到的是每行的所有信息,请问怎么写SQL语句?例如:表名:table 标识字段是:字段3 字段1 字段2 字段3 字段4
1 aaaa 1 ......
2 bbbb 1 ......
3 cccc 2 ......
4 dddd 3 ......期望得到的数据如下: 字段1 字段2 字段3 字段4
1 aaaa 1 ......
3 cccc 2 ......
4 dddd 3 ......
1 aaaa 1 ......
2 bbbb 1 ......
3 cccc 2 ......
4 dddd 3 ......期望得到的数据如下: 字段1 字段2 字段3 字段4
1 aaaa 1 ......
3 cccc 2 ......
4 dddd 3 ......
create table table1(a int,b varchar2(100),c int);
insert into table1
select 1,'aaaa',1 from dual union all
select 2,'bbbb',1 from dual union all
select 3,'cccc',2 from dual union all
select 4,'dddd',3 from dual;
--执行查询
select a,b,c from (select a,b,c,rank () over (partition by c order by b)
as ord from table1) where ord=1;
--查询结果
1 aaaa 1
3 cccc 2
4 dddd 3