Results 1 to 8 of 8

Thread: Select within select

  1. #1
    Junior Member
    Join Date
    Nov 2007
    Answers
    1

    Select within select

    Is there a way to write select statement within select statement?


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Select within select

    Yes you can write that.
    This is called nested sub query .
    Try the following sample to find out 3rd heighest sal form emp table.
    Code:
     select max(sal) from emp where sal< (select max(sal) from emp where sal<(select max(sal) from emp))



  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Select within select

    Subqueries are similar to SELECT chaining. While SELECT chaining combines SELECTs on the same level in a query, however, subqueries allow SELECTs to be embedded inside other queries. They can perform several functions:


    They can take the place of a constant.
    They can take the place of a constant yet vary based on the row being processed.
    They can return a list of values for use in a comparison.

    Examples
    SELECT name
    FROM customer
    WHERE customer.customer_id = (
    SELECT salesorder.customer_id
    FROM salesorder
    WHERE order_id = 14673
    );

    Subqueries as Correlated Values

    SELECT f1.firstname, f1.lastname, f1.age
    FROM friend f1
    WHERE age = (
    SELECT MAX(f2.age)
    FROM friend f2
    WHERE f1.state = f2.state
    )
    ORDER BY firstname, lastname;

    Subqueries as Lists of Values

    SELECT name
    FROM employee
    WHERE employee_id IN (
    SELECT employee_id
    FROM salesorder
    WHERE order_date = '7/19/1994'
    );

    other examples

    SELECT name
    FROM employee
    WHERE employee_id IN (
    SELECT employee_id
    FROM salesorder
    WHERE order_date = '7/19/1994'
    );

    SELECT name
    FROM employee
    WHERE employee_id = ANY (
    SELECT employee_id
    FROM salesorder
    WHERE order_date = '7/19/1994'
    );

    SELECT name
    FROM employee
    WHERE EXISTS (
    SELECT employee_id
    FROM salesorder
    WHERE salesorder.employee_id
    =employee.employee_id AND
    order_date = '7/19/1994'
    );


  4. #4
    Junior Member
    Join Date
    Nov 2007
    Answers
    1

    Re: Select within select

    to find nth highest salary

    select distinct (a.sal) from emp a where &n=select (count(distinct b.sal) from emp b where a.sal<=b.sal);


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Select within select

    You can write in this way also

    SELECT ename,job,sal
    FROM
    (
    SELECT *
    FROM emp
    ORDEY BY sal DESC
    )
    WHERE ROWNUM <=10;


  6. #6
    Contributing Member
    Join Date
    Jul 2007
    Answers
    42

    Re: Select within select

    Quote Originally Posted by krishnaindia2007 View Post
    You can write in this way also

    SELECT ename,job,sal
    FROM
    (
    SELECT *
    FROM emp
    ORDEY BY sal DESC
    )
    WHERE ROWNUM <=10;
    this is called inlineview.


  7. #7
    Junior Member
    Join Date
    Nov 2007
    Answers
    12

    Re: Select within select

    Quote Originally Posted by neeraj_sigh View Post
    Is there a way to write select statement within select statement?
    Yes, we can write Select statement within select statement.
    For instance, To get 2nd max sal we can use below query.
    Select max(sal) from emp where sal < (select max(sal) from emp).


  8. #8
    Contributing Member
    Join Date
    Oct 2007
    Answers
    88

    Smile Re: Select within select

    Quote Originally Posted by neeraj_sigh View Post
    Is there a way to write select statement within select statement?
    this is basic of sql ...it is nested query
    eX:select count(sname) from supply where pno in(select pno from pro where pname='pencil';


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact