How to Use Oracle Order by and Rownum Correctly

How to use Oracle ORDER BY and ROWNUM correctly?

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.

How to use order by and rownum without subselect?

Your current query (without the invalid ORDER BY) gets ORA-01427: single-row subquery returns more than one row. You can nest subqueries, but you can only refer back one level when joining; so if you did:

SELECT T1.CAMP_ID, T2.CAMP,
  (SELECT CAMP FROM
  FROM
    (SELECT OT.CAMP
    FROM OTHERTABLE OT
    WHERE OT.FK_TO_TABLE1=T1.CAMP_ID
    ORDER BY OT.ORDERCAMP
    )
  WHERE ROWNUM = 1)
FROM TABLE1 T1, TABLE2 T2 WHERE T1.FK_TO_T2=T2.PK;

... then you would get ORA-00904: "T1"."CAMP_ID": invalid identifier. Hence your question, presumably.

What you could do instead is join to the third table, and use the analytic ROW_NUMBER() function to assign the row number, and then use an outer select wrapped around the whole thing to only find the records with the lowest ORDERCAMP:

SELECT CAMP_ID, CAMP, OT_CAMP
FROM (
  SELECT T1.CAMP_ID, T2.CAMP, OT.CAMP AS OT_CAMP,
    ROW_NUMBER() OVER (PARTITION BY T1.CAMP_ID ORDER BY OT.ORDERCAMP) AS RN
  FROM TABLE2 T2
  JOIN TABLE1 T1 ON T1.FK_TO_T2=T2.PK
  JOIN OTHERTABLE OT ON OT.FK_TO_TABLE1=T1.CAMP_ID
)
WHERE RN = 1;

The ROW_NUMBER() can partition on the T1.CAMP_ID primary key value, or anything else that is unique.

SQL Fiddle demo, including the inner query run on its own so you can see the RN numbers assigned before the outer filter is applied.

Another approach is to use the aggregate KEEP DENSE_RANK FIRST function

SELECT T1.CAMP_ID, T2.CAMP,
  MAX(OT.CAMP) KEEP (DENSE_RANK FIRST ORDER BY OT.ORDERCAMP) AS OT_CAMP
FROM TABLE2 T2
JOIN TABLE1 T1 ON T1.FK_TO_T2=T2.PK
JOIN OTHERTABLE OT ON OT.FK_TO_TABLE1=T1.CAMP_ID
GROUP BY T1.CAMP_ID, T2.CAMP;

Which is a bit shorter and doesn't need an inner query. I'm not sure if there's any real advantage of one over the other.

SQL Fiddle demo.

Proper usage of ROWNUM in Oracle SQL query

The WHERE clause of your query is executed before ORDER BY so the ROWNUM = 1 is not the expected row. You can use FETCH FIRST ROW ONLY instead:

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) DESC, CITY 
FETCH FIRST ROW ONLY;

... or you put the statement in a sub-select and use WHERE with ROWNUM on this:

SELECT * 
FROM (
  SELECT CITY, LENGTH(CITY) 
  FROM STATION 
  ORDER BY LENGTH(CITY) DESC, CITY
)x WHERE ROWNUM = 1;

demo on dbfiddle.uk

How to get rows in the correct order when using RowNum in where clause

Since you're running on v11, you can't use FETCH but you can limit the sub-query.

select * from (
    SELECT S.LOGIN_ID as LoginId, S.ProductId as ProductId, P.CREATE_DT as CreateDate, 
      FROM SITE_USER S
     INNER JOIN Production P on P.LOGIN_ID = S.LOGIN_ID
     where P.PROCESS_CD = 'REGISTRATION' 
       and P.CREATE_DT >= '20-JAN-21'
     order by P.Request_id asc)
where rownum <= 10000;

UPD. And check please if this condition will work

and P.CREATE_DT >= '20-JAN-21'

if p.create_dt of date type, you need to update it to something similar to this

and P.CREATE_DT >= to_date('20-JAN-21', 'dd-MON-yy')

ORDER BY subquery and ROWNUM goes against relational philosophy?

The ORDER BY in this context is in effect Oracle's proprietary syntax for generating an "ordered" row number on a (logically) unordered set of rows. This is a poorly designed feature in my opinion but the equivalent ISO standard SQL ROW_NUMBER() function (also valid in Oracle) may make it clearer what is happening:

select *
from (
  select ROW_NUMBER() OVER (ORDER BY price) rn, *
  from table
) t
where rn <= 7;

In this example the ORDER BY goes where it more logically belongs: as part of the specification of a derived row number attribute. This is more powerful than Oracle's version because you can specify several different orderings defining different row numbers in the same result. The actual ordering of rows returned by this query is undefined. I believe that's also true in your Oracle-specific version of the query because no guarantee of ordering is made when you use ORDER BY in that way.

It's worth remembering that Oracle is not a Relational DBMS. In common with other SQL DBMSs Oracle departs from the relational model in some fundamental ways. Features like implicit ordering and DISTINCT exist in the product precisely because of the non-relational nature of the SQL model of data and the consequent need to work around keyless tables with duplicate rows.

Oracle ORDER BY with rownum or HAVING = ALL

There are two reasons your instructor is telling you that.

1. Data model. Relational DBMSs deal with sets, not lists. If you are learning SQL, it is better for you to think in terms of sets of tuples, that are unordered, than in order lists. You will be better at understanding how to query the DBMS. I consider your solution a hack: one that works, partially, since--as Perun_x has pointed out--- it does not work if more than one tuple match the result. It is contrary to the data model and spirit of SQL).

2. Portability. This is the real killer. Your code will work on Oracle but not in other DBMSs that do not support the row_number attribute (each has its own way to do it).

--dmg

Clarifying rownum order of operations in Oracle

The AND has no role in this. When result set is being constructed, the rownum is assigned to the results before outermost ordering. Filtering on ROWNUM is a hard stop from feeding results up from deeper in the execution plan. Therefore for example a construct like where rownum > 5 returns no rows.

Hopefully this helps. If not, please elaborate in your question and/or explain why you are asking. There are alternatives that are sometimes better, such as row_number().

---

Related Topics