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

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

以下是我用来获取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.