Thread: Select within select

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

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))

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'
);

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);

You can write in this way also

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

Originally Posted by krishnaindia2007

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.

Originally Posted by neeraj_sigh

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).

Originally Posted by neeraj_sigh

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';