# Databases > SQL Select within select

## neeraj_sigh

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

----------


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

----------


## purnimamca

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

----------


## krishnaindia2007

You can write in this way also

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

----------


## priyasp_msit

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

----------


## prashanth.n

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

----------


## sarathi trichy

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

----------

