如何select出一张表内某个字段是最小的，或者是空的那条纪录（该字段可能有null值）内有详细描述！！！

Question

select * from t (
  select tab.*,rank() over (partition f2 order by f3 asc) rk ) t where t.rk=1;

Answer 1

select f2,min(f3) from tab group by f2

Answer 2

select * from tab a where a.f2 is null
union all
select * from tab b where b.f2,b.f3,b.f4 in (select c.f2,min(c.f3),c.f4 from tab c group by c.f2,c.f4) and b.f3 is not null

Answer 3

SQL> select * from testd;F1                   F2                   F3                   F4
-------------------- -------------------- -------------------- --------------------
1                    小王                 2                    局长
2                    小王                 4                    科长
3                    小王                 5                    厅长
4                    小李                 1                    省长
5                    小李                 2                    局长
6                    小张                                      科员6 rows selectedSQL> 
SQL> select * from  (
  2    select testd.*,rank() over (partition by f2 order by f3 asc) rk  from testd) t where t.rk=1 order by f1;F1                   F2                   F3                   F4                           RK
-------------------- -------------------- -------------------- -------------------- ----------
1                    小王                 2                    局长                          1
4                    小李                 1                    省长                          1
6                    小张                                      科员                          1

Answer 4

使用分析函数first_value实现取出排名的方式.
[code]
12:47:11 SQL> select * from t;        F1 F2                           F3 F4
---------- -------------------- ---------- --------------------
         1 小王                          2 局长
         2 小王                          4 科长
         3 小王                          5 厅长
         4 小李                          1 省长
         5 小李                          2 局长
         6 小张                            科员6 rows selected.Elapsed: 00:00:00.00
12:47:29 SQL> select distinct
12:47:38   2    first_value(f1) over (partition by f2 order by f3 nulls first) f1,
12:47:52   3    f2,
12:47:56   4    first_value(f3) over (partition by f2 order by f3 nulls first) f3,
12:48:07   5    first_value(f4) over (partition by f2 order by f3 nulls first) f4
12:48:15   6  from t
12:48:16   7  /        F1 F2                           F3 F4
---------- -------------------- ---------- --------------------
         4 小李                          1 省长
         1 小王                          2 局长
         6 小张                            科员Elapsed: 00:00:00.00
12:48:16 SQL>
[/code]