如何正确使用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
但是 Oracle:
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
语句在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屋!