select top 1 salary from (select top 5 salary from 
emp_table order by salary desc)temptable order by salary asc

select top 10 from tbl_name order by salary desc

To find nth maximum salry

Select salary from tbl_name a where
n-1=(Select count(distinct(count(*))) from tbl_name b
where b.salary>a.salary)

To find second highest salary
select max(sal) from emp where sal not in (select max(sal) 
from emp)

to find order
 select top 5 8 from emp order by sal desc

To find any number of Salary..

Select * From Table E1 Where
    (n-1) = (Select Count(Distinct(E2.Sal)) From Table E2 
Where E2.Sal> E1.Sal)


**Vinay Singh

select top 1 sal from emp where empid in(select top 2 empid
from emp order by sal desc) order by
sal asc

if you want to get 3rd one than put 'top 3' in subquery and
same for 4th, 5th....

first find second largest salary in the emp database:

select min(sal)sal from(select sal from emp order by sal 
desc)
where rownum<3
u can find 3rd,4th... so on ,only change rownum
like rownum<4,rownum<5 ... so on

another way u can find 2nd largest salary,3rd largest 
salary ... so on

 select sal from(select sal from emp order by sal desc)
where rownum<3
 minus
 select sal from(select sal from emp order by sal desc)
where rownum<2

only change rownum to find various largest salary

Subhranghshu Bhattacharjee

TO FIND THE SECOND HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select max(sal) 
from emp)

TO FIND THE 3RD HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select distinct 
top 2 sal from emp order by sal desc)

TO FIND THE Nth HIGHEST SAL IN EMP TABLE

select max(sal) from emp where sal not in(select distinct 
top N-1 sal from emp order by sal desc)

yagneswara babu
yagnesh_sn@yahoo.co.in

with ttble as 
(
select top 3 empid,empname,empsalary
from 
emptable 
order by empsalary desc
)
-- above will create a common table expression with name
--ttable 
-- to know about it search for commontable expression 
select top 1 empid,empname,empsalary 
from
ttable 
order by empsalary asc

select salary from emp
where salary = (select max(salary) from emp
                  where salary <>(select max(salary) from emp)

1. solution taken as std_id instead of salary
select max( b.std_id)   from std_info a 
inner join std_info b on a.std_id <> b.std_id 
where a.std_id > b.std_id 

2. solution
select max(std_id) from std_info  
where std_id < ( select max(std_id) from std_info)

select max(sal) 
from (select rownum 
         from emp 
            order by sal desc)
             where rownum=2

for second largest salary
Select * from emp a where 2=(select count(*) from emp b
where a.sal<=b.sal)

Above mentioned query can also be used for 3 ,4 ..n

Select * from emp a where 3=(select count(*) from emp b
where a.sal<=b.sal)

select p.emp_id,p.name,p.salary from
( select emp_id,name,salary,dense_rank()over( order by 
salary desc)rank
from empTable )p
where rank=2

-- Create table #temp 
-- (
--  Emp_id int identity(1,1),
--  Emp_Name varchar (100) ,
--  Emp_Salary int 
-- )

-- 
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('kunal', '2000')  
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('aditya', '5000')  
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('abhishek', '6000')  
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('kailash', '5000')  
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('rohit', '8000')  
-- Insert into #Temp (Emp_Name, Emp_Salary) values 
('rakesh', '3000')  

Select a.* From #temp a,
(Select Max(a.Emp_salary)Emp_Salary From #temp a,
(Select Max(x.Emp_salary)Emp_Salary From #temp a,
(Select a.* From #temp a,(Select Max (Emp_Salary)Emp_Salary 
From #Temp) b
Where a.Emp_Salary < b.Emp_Salary ) x ) y
where a.Emp_Salary < y.Emp_Salary)Z
where  a.Emp_Salary = z.Emp_Salary

create table #a(rn varchar(30),salary int)
insert into #a values('a',10000)
insert into #a values('b',12000)
insert into #a values('c',15000)
insert into #a values('e',25000)
insert into #a values('d',22000)
insert into #a values('f',30000)
insert into #a values('h',35000)
insert into #a values('g',32000)
insert into #a values('i',32000)
select * from #a  order by salary desc
Select * from #a a where 1=(select count(distinct b.salary) 
from #a b where  a.salary <=b.salary) 
Select * from #a a where 2=(select count(distinct b.salary) 
from #a b where  a.salary <=b.salary) 
Select * from #a a where 3=(select count(distinct b.salary) 
from #a b where  a.salary <=b.salary) 
Select * from #a a where 4=(select count(distinct b.salary) 
from #a b where  a.salary <=b.salary)

SELECT MAX(VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO WHERE VCRG_ID< (SELECT MAX (VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO)