- 论坛徽章:
- 0
|
如果要根据no做group by,然后根据名称进行排序,取有序的第一条或者多少条,可以考虑使用rank。
范例:
CREATE TABLE TEST1(NO VARCHAR2(10),NAME VARCHAR2(10));
insert into test1 values('80','sh1');
insert into test1 values('70','sh1');
insert into test1 values('80','ab1');
insert into test1 values('60','dd1');
commit;
SQL> select * from test1;
NO NAME
---------- ----------
80 sh1
70 sh1
80 ab1
60 dd1
SQL> SELECT NO, NAME
2 FROM (SELECT NO, NAME,
3 RANK () OVER (PARTITION BY NO ORDER BY NAME ASC) AS row_rank
4 FROM TEST1)
5 WHERE row_rank = 1;
NO NAME
---------- ----------
60 dd1
70 sh1
80 ab1 |
|