Home » SQL & PL/SQL » SQL & PL/SQL » Subquery in Order by Clause (Oracle 9i)
Subquery in Order by Clause [message #396473] Mon, 06 April 2009 20:46 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Can Subquery placed in the Order By Clause? and what is the use of it?
Re: Subquery in Order by Clause [message #396474 is a reply to message #396473] Mon, 06 April 2009 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ygsunilkumar wrote on Mon, 06 April 2009 18:46
Can Subquery placed in the Order By Clause? and what is the use of it?

Are you incapable or unwilling to Read The Fine Manual to obtain answers?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: Subquery in Order by Clause [message #396775 is a reply to message #396474] Wed, 08 April 2009 00:57 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
In the link there is no information about Subqueries in Order By Clause. As per my knowledge in Order By clause Subqueries cannot be used because based on the result of the subquery the rows cannot be sorted if I am wrong please give me correct answer?
Re: Subquery in Order by Clause [message #396781 is a reply to message #396775] Wed, 08 April 2009 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ygsunilkumar wrote on Wed, 08 April 2009 07:57
In the link there is no information about Subqueries in Order By Clause. As per my knowledge in Order By clause Subqueries cannot be used because based on the result of the subquery the rows cannot be sorted if I am wrong please give me correct answer?

What happen when you tried?

Regards
Michel


Re: Subquery in Order by Clause [message #396785 is a reply to message #396775] Wed, 08 April 2009 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is this example what you are looking for? If so, then
a) subquery can be used in an ORDER BY clause
b) it is used to sort records
SQL> select e.deptno, e.ename, e.sal
  2  from emp e
  3  order by (select e1.sal
  4            from emp e1
  5            where e1.empno = e.empno
  6           ) desc;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5800
        20 FORD             3800
        20 SCOTT            3800
        20 JONES            3775
        30 BLAKE            3650
        10 CLARK            3250
        30 ALLEN            2400
        30 TURNER           2300
        10 MILLER           2100
        30 WARD             2050
        30 MARTIN           2050
        20 ADAMS            1900
        30 JAMES            1750
        20 SMITH            1600

14 rows selected.

SQL>
Re: Subquery in Order by Clause [message #396802 is a reply to message #396785] Wed, 08 April 2009 01:43 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I am getting error. How to handle this if i use IN operator also getting error.

Code removed at posters request, due to confidentiality issues

[Updated on: Mon, 11 May 2009 03:12] by Moderator

Report message to a moderator

Re: Subquery in Order by Clause [message #396804 is a reply to message #396473] Wed, 08 April 2009 01:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01427: single-row subquery returns more than one row

Provide SELECT that only returns 1 row!
Re: Subquery in Order by Clause [message #396829 is a reply to message #396804] Wed, 08 April 2009 02:24 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Its understood that Multipl-row subquery cannot be used in Order By Clause. Please confirm?
Re: Subquery in Order by Clause [message #396840 is a reply to message #396829] Wed, 08 April 2009 02:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Which of the returned values should be used to give a row in the main query a sequence number in the order?
Re: Subquery in Order by Clause [message #396845 is a reply to message #396829] Wed, 08 April 2009 02:49 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ygsunilkumar wrote on Wed, 08 April 2009 09:24
Its understood that Multipl-row subquery cannot be used in Order By Clause. Please confirm?

The subquery has to return (at most) one row for each row in the resultset.

It is more logical problem than the Oracle one. For example, if RECEIVED_DCIS would contain these rows:
PATIENT RECEIVED_DCI_STATUS_CODE
------- ------------------------
      1 1
      1 10
      2 8
      3 2
      3 4
      3 6
, how would you sort patients based on these received dci status codes?
Previous Topic: Insert into select clause is taking long time than the select [merged]
Next Topic: When we cant apply self join
Goto Forum:
  


Current Time: Sun Dec 11 06:21:49 CST 2016

Total time taken to generate the page: 0.07412 seconds