Oracle SQL-将结果限制为最大值

Oracle SQL - limit results to max value(Oracle SQL-将结果限制为最大值)

问题描述

我正在使用查询：

select
  SGB_ID,
  max(SGB_TERM_CODE_EFF)max_term,
  SGB_TYP_CODE
from SGB
group by
  SGB_ID,
  SGB_TYP_CODE
order by 1

我得到多行，因为SGB_TYP_CODE具有不同的值。我只想要最高任期的结果。我试过使用‘Keep Density_RANK’，但我无法使用它。

谢谢。

推荐答案

下面是如何使用max()...Keep()：

SELECT sgb_id,
       MAX (sgb_term_code_eff) max_term,
       MAX (sgb_typ_code) 
            KEEP ( DENSE_RANK FIRST 
                   ORDER BY sgb_term_code_eff DESC ) sgb_typ_code
FROM   sgb
GROUP BY sgb_id
ORDER BY 1

完整示例：

with sgb ( sgb_id, sgb_term_code_eff, sgb_typ_code ) AS 
 ( SELECT 1, 'A', 'ACODE' FROM DUAL UNION ALL
   SELECT 1, 'B', 'BCODE' FROM DUAL UNION ALL
   SELECT 1, 'Z', 'ZCODE' FROM DUAL UNION ALL
   SELECT 1, 'D', 'DCODE' FROM DUAL UNION ALL
   SELECT 2, 'A', 'ACODE' FROM DUAL UNION ALL
   SELECT 2, 'Q', 'QCODE' FROM DUAL UNION ALL
   SELECT 2, 'Q', 'QCODE' FROM DUAL UNION ALL
   SELECT 3, 'A', 'ACODE' FROM DUAL )
SELECT sgb_id,
       MAX (sgb_term_code_eff) max_term,
       MAX (sgb_typ_code) KEEP ( DENSE_RANK FIRST ORDER BY sgb_term_code_eff DESC ) sgb_typ_code
FROM   sgb
GROUP BY sgb_id
ORDER BY 1

SGB_ID                                 MAX_TERM SGB_TYP_CODE 
-------------------------------------- -------- ------------ 
                                     1 Z        ZCODE
                                     2 Q        QCODE
                                     3 A        ACODE

这篇关于Oracle SQL-将结果限制为最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！