Oracle 取排行榜

WITH qa AS
     (SELECT '國文' cid, 60 ca, TRUNC (SYSDATE) cb
        FROM DUAL
      UNION
      SELECT '國文' cid, 100 ca, TRUNC (SYSDATE) + 1 cb
        FROM DUAL
      UNION
      SELECT '國文' cid, 60 ca, TRUNC (SYSDATE) + 3 cb
        FROM DUAL
      UNION
      SELECT '國文' cid, 100 ca, TRUNC (SYSDATE) + 3 cb
        FROM DUAL
      UNION
      SELECT '數學' cid, 40 ca, TRUNC (SYSDATE) + 3 cb
        FROM DUAL
      UNION
      SELECT '數學' cid, 70 ca, TRUNC (SYSDATE) + 2 cb
        FROM DUAL
      UNION
      SELECT '數學' cid, 90 ca, TRUNC (SYSDATE) + 3 cb
        FROM DUAL
        UNION
      SELECT '數學' cid, NULL ca, TRUNC (SYSDATE) + 3 cb
        FROM DUAL),
     qb AS
     (SELECT DENSE_RANK () OVER (PARTITION BY cid ORDER BY ca DESC  NULLS LAST) AS dense_rank_top,
             RANK () OVER (PARTITION BY cid ORDER BY ca DESC  NULLS LAST) AS rank_top,
             MAX(CA) KEEP (DENSE_RANK LAST ORDER BY CA  NULLS LAST) OVER (PARTITION BY CID) "Highest" ,
             MIN(ca) KEEP (DENSE_RANK FIRST ORDER BY ca  NULLS LAST) OVER (PARTITION BY cid) "Lowest",qa.*
        FROM qa)
SELECT *
  FROM qb



DENSE_RANK_TOP RANK_TOP Highest Lowest CID CA CB
1 1 100 60 國文 100 2010/4/29
1 1 100 60 國文 100 2010/5/1
2 3 100 60 國文 60 2010/4/28
2 3 100 60 國文 60 2010/5/1
1 1 40 數學 90 2010/5/1
2 2 40 數學 70 2010/4/30
3 3 40 數學 40 2010/5/1
4 4 40 數學 2010/5/1

同行資料列欄位取最大值與最小值

GREATEST ( expr_list )
LEAST ( expr_list ) 

SQL> select LEAST(1,2,3,5,4,7,6) from dual;
 
LEAST(1,2,3,5,4,7,6)
--------------------

SQL> select GREATEST(1,10,3,5,5,1,6) from dual;
 
GREATEST(1,10,3,5,5,1,6)
------------------------
                      10

TBC 家庭日

袋鼠跳