本文介绍了获取结果集的最后一条记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下是我用来获取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.
更多推荐
获取结果集的最后一条记录
发布评论