Order by in sub query using oracle-CodePudding

I tried to get value from sub query after ordering the records of it but the following occurred when execute the query : ORA-00907: missing right parenthesis

The Query is :

select S.value , nvl((select D.value  from D
                join  T on D.subID = t.SubID
                where  D.subid2 = s.subid2 and t.subid3 = s.subid3    and rownum = 1 order by t.id),0 ) value

from   S

CodePudding user response：

You can't have ORDER BY clause in a subquery.

See if something like this helps: use a CTE (as it looks somewhat nicer; could be a normal subquery, if you want) which calculates ordinal number for all rows, sorted by t.id column value. In outer (main) query, select row whose rn = 1 (which should act just like your ORDER BY t.id rownum = 1).

WITH
   temp
   AS
      (SELECT s.VALUE s_value,
              d.VALUE d_value,
              ROW_NUMBER () OVER (ORDER BY t.id) rn
         FROM d
              JOIN t ON d.subid = t.subid
              JOIN s
                 ON     s.subid2 = d.subid2
                    AND s.subid3 = t.subid3)
SELECT s_value, NVL (d_value, 0) d_value
  FROM temp
 WHERE rn = 1

CodePudding user response：

If you are on Oracle 12c or higher, you can use the FETCH FIRST... clause.

SELECT S.VALUE,
       NVL ((  SELECT D.VALUE
                 FROM D JOIN T ON D.subID = t.SubID
                WHERE D.subid2 = s.subid2 AND t.subid3 = s.subid3
             ORDER BY t.id
                FETCH FIRST 1 ROWS ONLY),
            0)    VALUE
  FROM S