blocks|key|198862|text|在order+by之前执行where语句。因此，您想要的查询是“获取第一行，然后按t_stamp描述对其进行排序”。而这并不是你想要的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|198863|subquery方法是在Oracle中执行此操作的适当方法。|198864|如果你想要一个在两台服务器上都能工作的版本，你可以使用：|198865|select+ril.*
from+(select+ril.*,+row_number()+over+(order+by+t_stamp+desc)+as+seqnum
++++++from+raceway_input_labo+ril
+++++)+ril
where+seqnum+=+1|code-block|syntax|javascript|198866|外部*将在最后一列返回"1“。您需要单独列出列以避免这种情况。|198867|entityMap^0|1|8|D|5|15|7|0|0|0|0|2|1|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|11|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|R|$]]

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

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:

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

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

blocks|key|3036330|text|请改用ROW_NUMBER()。ROWNUM是一个伪列，ROW_NUMBER()是一个函数。您可以阅读它们之间的差异，并查看以下查询的输出差异：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3036331|SELECT+*+FROM+(SELECT+rownum,+deptno,+ename
+++++++++++FROM+scott.emp
++++++++ORDER+BY+deptno
+++++++)
+WHERE+rownum+<=+3
+/

ROWNUM++++DEPTNO++++ENAME
---------------------------
+7++++++++10++++CLARK
+14+++++++10++++MILLER
+9++++++++10++++KING


+SELECT+*+FROM+
+(
++SELECT+deptno,+ename
+++++++,+ROW_NUMBER()+OVER+(ORDER+BY+deptno)+rno
++FROM+scott.emp
+ORDER+BY+deptno
+)
WHERE+rno+<=+3
/

DEPTNO++++ENAME++++RNO
-------------------------
10++++CLARK++++++++1
10++++MILLER+++++++2
10++++KING+++++++++3|code-block|syntax|javascript|3036332|entityMap^0|3|C|G|6|S|C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

Use <code>ROW_NUMBER()</code> instead. <code>ROWNUM</code> is a pseudocolumn and <code>ROW_NUMBER()</code> is a function. You can read about difference between them and see the difference in output of below queries:

<pre><code>SELECT * FROM (SELECT rownum, deptno, ename
 FROM scott.emp
 ORDER BY deptno
 )
 WHERE rownum &lt;= 3
 /

ROWNUM DEPTNO ENAME
---------------------------
 7 10 CLARK
 14 10 MILLER
 9 10 KING


 SELECT * FROM 
 (
 SELECT deptno, ename
 , ROW_NUMBER() OVER (ORDER BY deptno) rno
 FROM scott.emp
 ORDER BY deptno
 )
WHERE rno &lt;= 3
/

DEPTNO ENAME RNO
-------------------------
10 CLARK 1
10 MILLER 2
10 KING 3
</code></pre>

blocks|key|3036376|text|在上面的评论中记录了这方面的几个设计问题。简而言之，在Oracle中，当您有大型表和/或具有相同列名的表时，您需要手动限制结果(并且您不希望显式地将它们全部键入并重命名它们)。简单的解决方案是找出断点并将其限制在查询中。或者，如果没有冲突的列名约束，也可以在内部查询中执行此操作。例如。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3036377|WHERE+m_api_log.created_date+BETWEEN+TO_DATE('10/23/2015+05:00',+'MM/DD/YYYY+HH24:MI')+
+++++++++++++++++++++++++++++++++AND+TO_DATE('10/30/2015+23:59',+'MM/DD/YYYY+HH24:MI')++|code-block|syntax|javascript|3036378|会大大减少结果。然后，您可以执行ORDER+BY，甚至执行外部查询来限制行。|3036379|此外，我认为TOAD有一个限制行的功能；但是，不确定在Oracle上的实际查询中是否有限制。不确定。|3036380|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Documented couple of design issues with this in a comment above. Short story, in Oracle, you need to limit the results manually when you have large tables and/or tables with same column names (and you don't want to explicit type them all out and rename them all). Easy solution is to figure out your breakpoint and limit that in your query. Or you could also do this in the inner query if you don't have the conflicting column names constraint.
E.g. 

<pre><code>WHERE m_api_log.created_date BETWEEN TO_DATE('10/23/2015 05:00', 'MM/DD/YYYY HH24:MI') 
 AND TO_DATE('10/30/2015 23:59', 'MM/DD/YYYY HH24:MI') 
</code></pre>

will cut down the results substantially. Then you can ORDER BY or even do the outer query to limit rows.

Also, I think TOAD has a feature to limit rows; but, not sure that does limiting within the actual query on Oracle. Not sure.

blocks|key|3036421|text|在这个用例中，我建议的另一种方法是使用MAX(t_stamp)来获取最新的行...例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3036422|select+t.*+from+raceway_input_labo+t
where+t.t_stamp+=+(select+max(t_stamp)+from+raceway_input_labo)+
limit+1|code-block|syntax|javascript|3036423|我的编码模式偏好(也许)-可靠，通常比试图从排序列表中选择第一行更好-而且意图更明确可读。|3036424|希望这能帮到你。|3036425|SQLer|3036426|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

An alternate I would suggest in this use case is to use the MAX(t_stamp) to get the latest row ... e.g. 

<pre><code>select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo) 
limit 1
</code></pre>

My coding pattern preference (perhaps) - reliable, generally performs at or better than trying to select the 1st row from a sorted list - also the intent is more explicitly readable. 
Hope this helps ... 

SQLer

blocks|key|194650|text|自从Oracle12c以来，我们现在有了row+limiting+clauses，它就是这样做的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|194651|SELECT+*
FROM+raceway_input_labo+
ORDER+BY+t_stamp+DESC
FETCH+FIRST+ROW+ONLY|code-block|syntax|javascript|194652|或者用于不同场景的多个alternatives+(前n行、平局处理等)。|194653|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC|1|https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/first-n-rows/index^0|K|K|0|0|0|B|C|1|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]]]

Since Oracle 12c we now have <a href="https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC" rel="nofollow noreferrer">row limiting clauses</a> which do exactly this.
<pre><code>SELECT *
FROM raceway_input_labo 
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY
</code></pre>
Or many <a href="https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/first-n-rows/index" rel="nofollow noreferrer">alternatives</a> for different scenarios (first n rows, tie handling, etc.).

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:

<pre><code>SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC
</code></pre>

=> That will returns me the most recent record

But Oracle:

<pre><code>SELECT *
FROM raceway_input_labo 
WHERE rownum &lt;= 1
ORDER BY t_stamp DESC
</code></pre>

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

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

<pre><code>SELECT * 
FROM 
 (SELECT *
 FROM raceway_input_labo 
 ORDER BY t_stamp DESC)
WHERE rownum &lt;= 1
</code></pre>

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 ?

How to use Oracle ORDER BY and ROWNUM correctly?

SQL Server

服务器

我很难将存储过程从SQL Server转换到Oracle，以便让我们的产品与之兼容。我有基于时间戳返回某些表的最新记录的查询：SQL Server:SELECT TOP 1 *FROM RACEWAY_INPUT_LABOORDER BY t_stamp DESC将返回最新记录的=>但是甲骨文：SELECT *FROM...

问如何正确使用Oracle ORDER BY和ROWNUM？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何正确使用Oracle ORDER BY和ROWNUM？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何正确使用Oracle ORDER BY和ROWNUM？
EN