获取结果集的最后一条记录 [英] Get last record of result set

问题描述

以下是我用来获取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 = 4920111 But the last row with the HAZMAT_PLAYCARD_NOTATION value SPONTANEOUSLY COMBUSTIBLE should be retrieved. That's the objective

推荐答案

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

Get last record of result set

有很多方法:

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.

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