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)) [/code]

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;

[QUOTE=krishnaindia2007;20807]You can write in this way also

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

this is called inlineview.

[QUOTE=neeraj_sigh;20384]Is there a way to write select statement within select statement?[/QUOTE]

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

[QUOTE=neeraj_sigh;20384]Is there a way to write select statement within select statement?[/QUOTE]

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