如何正确使用 Oracle ORDER BY 和 ROWNUM? [英] How to use Oracle ORDER BY and ROWNUM correctly?

问题描述

我很难将存储过程从 SQL Server 转换为 Oracle，以使我们的产品与之兼容.

I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it.

我有一些查询会根据时间戳返回某些表的最新记录:

I have queries which returns the most recent record of some tables, based on a timestamp :

SQL Server:

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

=> 这将返回我最近的记录

=> That will returns me the most recent record

但是甲骨文:

SELECT *
FROM raceway_input_labo 
WHERE  rownum <= 1
ORDER BY t_stamp DESC

=> 这将返回最旧的记录(可能取决于索引)，无论 ORDER BY 语句如何！

=> That will returns me the oldest record (probably depending on the index), regardless the ORDER BY statement!

我以这种方式封装了 Oracle 查询以符合我的要求:

I encapsulated the Oracle query this way to match my requirements:

SELECT * 
FROM 
    (SELECT *
     FROM raceway_input_labo 
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

它的工作原理.但这对我来说听起来像是一个可怕的黑客，特别是如果我在涉及的表中有很多记录.

and it works. But it sounds like a horrible hack to me, especially if I have a lot of records in the involved tables.

实现这一目标的最佳方法是什么?

What is the best way to achieve this ?

推荐答案

where 语句在 order by 之前执行.因此，您想要的查询是取第一行，然后按 t_stamp desc 排序".这不是你想要的.

The where statement gets executed before the order by. So, your desired query is saying "take the first row and then order it by t_stamp desc". And that is not what you intend.

子查询方法是在 Oracle 中执行此操作的正确方法.

The subquery method is the proper method for doing this in Oracle.

如果你想要一个在两台服务器上都能运行的版本，你可以使用:

If you want a version that works in both servers, you can use:

select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
      from raceway_input_labo ril
     ) ril
where seqnum = 1

外部 * 将在最后一列返回1".您需要单独列出列以避免这种情况.

The outer * will return "1" in the last column. You would need to list the columns individually to avoid this.

这篇关于如何正确使用 Oracle ORDER BY 和 ROWNUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！