获取结果集的最后一条记录

获取结果集的最后一条记录

本文介绍了获取结果集的最后一条记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

以下是我用来获取select查询返回结果的最后一条记录的sql查询(oracle).这是最佳方法吗?

Following is the sql query (oracle) that I use to get the last record of the returning result of the select query. Is this the optimum way?

SELECT HAZMAT_PLACARD_NOTATION
        INTO v_pcn
        FROM HAZMAT_CLASS_IRF
        WHERE HAZMAT_CD = p_stcc_cd and ROWID = (SELECT MAX(ROWID) FROM HAZMAT_CLASS_IRF WHERE HAZMAT_CD = p_stcc_cd);

以下是选择查询的示例结果集,其中HAZMAT_CD = 4920111但是,应该检索具有HAZMAT_PLAYCARD_NOTATION值自发可燃的最后一行.这就是目标

Following is an example result set of the select query where HAZMAT_CD = 4920111But the last row with the HAZMAT_PLAYCARD_NOTATION value SPONTANEOUSLY COMBUSTIBLE should be retrieved. That's the objective

推荐答案

有很多方法:

Oracle Pre-12c版本:

    子查询中的
  • ROWNUM ,外部查询中的 ORDER BY
  • 分析功能
  • ROWNUM in sub-query and ORDER BY in outer query
  • Analytic function

Oracle 12c版本:

  • 前n行限制功能

使用 ROWNUM

SELECT HAZMAT_PLACARD_NOTATION
INTO v_pcn
FROM
  (SELECT HAZMAT_PLACARD_NOTATION,
    ROWNUM rn
  FROM HAZMAT_CLASS_IRF
  WHERE HAZMAT_CD = p_stcc_cd
  ORDER BY        <sort_column> DESC
  )
WHERE rn =1;

使用前n行限制功能

SELECT HAZMAT_PLACARD_NOTATION
 INTO v_pcn
  FROM HAZMAT_CLASS_IRF
  WHERE HAZMAT_CD = p_stcc_cd
  ORDER BY        <sort_column> DESC
FETCH FIRST 1 ROW ONLY;

请查看此答案以获取示例和详细说明.

Have a look at this answer for examples and detailed explanation.

这篇关于获取结果集的最后一条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-19 11:28