Menu
HAVING refers to properties of groups created by GROUP BY , not to individual rows. You can't use ROWNUM in HAVING any more than you can use BYTES , or any other expression that may have different values for rows within a single group.
ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause , beginning with 1.
From a little reading, ROWNUM is a value automatically assigned by Oracle to a rowset (prior to ORDER BY being evaluated, so don't ever ORDER BY ROWNUM or use a WHERE ROWNUM < 10 with an ORDER BY ). ROW_NUMBER() appears to be a function for assigning row numbers to a result set returned by a subquery or partition.
Oracle's ROWNUM starts on 1 and is only incremented when assigned to a row that passes the WHERE condition. Since you're filtering on ROWNUM=2, ROWNUM=1 doesn't pass the WHERE condition, and ROWNUM is therefore never assigned to a row and incremented to 2.
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.
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
The outer * will return "1" in the last column. You would need to list the columns individually to avoid this.
Use ROW_NUMBER() instead. ROWNUM is a pseudocolumn and ROW_NUMBER() is a function. You can read about difference between them and see the difference in output of below queries:
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
Since Oracle 12c we now have row limiting clauses which do exactly this.
SELECT *
FROM raceway_input_labo
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY
Or many alternatives for different scenarios (first n rows, tie handling, etc.).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With