Select within select

[debasisdas]

Yes you can write that.
This is called nested sub query .
Try the following sample to find out 3rd heighest sal form emp table.

 select max(sal) from emp where sal< (select max(sal) from emp where sal<(select max(sal) from emp))

[susarlasireesha]

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