How to use order by and rownum without subselect?

Question

I need to build a query with a order by and rownum but without use a sublect. It is needed to get the first row of the query ordered.

In other words, I want the result of

select * from (
SELECT CAMP1,ORDERCAMP
FROM TABLENAME
ORDER BY ORDERCAMP) where rownum=1;

but whithout use a subselect. Is it possible?

I have a Oracle 11. You could say this is my whole query:

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

The subquery returns more than one row, and I cant use another subquery like

SELECT T1.CAMP_ID,
  T2.CAMP
  (SELECT *
  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;
SELECT CAMP1,ORDERCAMP FROM TABLE2 ORDER BY ORDERCAMP

Because the T1.CAMP_ID is an invalid identifier in the third level subquery.

I hope I have explained myself enough.

Answer 1

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.