How to use Oracle ORDER BY and ROWNUM correctly?

by

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.

More Related Contents:

  1. How ROWNUM works in pagination query?
  2. SQL ROWNUM how to return rows between a specific range
  3. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  4. Selecting the second row of a table using rownum
  5. Custom Order in Oracle SQL
  6. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  7. Curious issue with Oracle UNION and ORDER BY
  8. Receive an unexpected symbol error in my PLSQL query [closed]
  9. SQL Query to concatenate column values from multiple rows in Oracle
  10. Select First Row of Every Group in sql [duplicate]
  11. Paging with Oracle
  12. Comparing Dates in Oracle SQL
  13. Oracle Differences between NVL and Coalesce
  14. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  15. SQL – How to store and navigate hierarchies?
  16. GROUP BY / aggregate function confusion in SQL
  17. Oracle “(+)” Operator
  18. Calculating difference between two timestamps in Oracle in milliseconds
  19. Optimize query with OFFSET on large table
  20. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  21. SQL Error: ORA-01861: literal does not match format string 01861
  22. Determine Oracle null == null
  23. How to anticipate and escape single quote ‘ in oracle
  24. “Order By” using a parameter for the column name
  25. Oracle insert if row does not exist
  26. SELECT INTO using Oracle
  27. `show create table` equivalent in oracle sql
  28. Query json dictionary data in SQL
  29. SQL for ordering by number – 1,2,3,4 etc instead of 1,10,11,12
  30. ORA-00972 identifier is too long alias column name

Leave a Comment