How to find the second largest salary in the emp database and
also How to find 3rd,4th and so on ........ in the emp database
plz mail the answer @ mak2786@gmail.com
Answers were Sorted based on User's Feedback
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
Is This Answer Correct ? | 39 Yes | 6 No |
Answer / saikrishna reddy .k
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
Is This Answer Correct ? | 30 Yes | 17 No |
Answer / punit kumar
1.To find second highest salary
select max(sal) from emp_record
where sal not in (select max(sal)from emp_record)
2.To find second highest salary
select max(sal) from emp_record
where sal < (select max(sal)from emp_record)
3.create table punit(rn varchar(30),salary int)
insert into punit values('a',10000)
insert into punit values('b',12000)
insert into punit values('c',15000)
insert into punit values('e',25000)
insert into punit values('d',22000)
insert into punit values('f',30000)
insert into punit values('h',35000)
insert into punit values('g',32000)
insert into punit values('i',32000)
select * from punit order by salary desc
Select * from punit a where 1=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 2=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 3=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
Select * from punit a where 4=(select count(distinct b.salary)
from punit b where a.salary <=b.salary)
by:-
PUNIT CHAUHAN
DELHI
MCA STUDENT STUDIS IN NOIDA
Is This Answer Correct ? | 9 Yes | 0 No |
Answer / neon
select salary from emp
where salary = (select max(salary) from emp
where salary <>(select max(salary) from emp)
Is This Answer Correct ? | 8 Yes | 3 No |
Answer / subhranghshu bhattacharjee
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
Is This Answer Correct ? | 10 Yes | 8 No |
Answer / amar banerjee
sol1> select MAX(salary) from emp where salary< (select MAX(salary) from emp);
sol2> select salary from emp sort by salary desc limit 1,1;
for finding nth position
select salary from emp sort by salary desc limit n-1,1;
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / pankaj arya
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....
Is This Answer Correct ? | 15 Yes | 14 No |
Answer / prafulla borade
SELECT MAX(VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO WHERE VCRG_ID< (SELECT MAX (VCRG_ID) FROM dbo.TRN_VEHICLE_CARGO)
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / prathapshankarshiva
select max(salary) from (select salary from table1 where
salary<(select max(salary) from table1))
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / narendra soni
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)
Is This Answer Correct ? | 2 Yes | 2 No |
What is purpose of normalization?
How to concatenate two binary strings together?
What are the characteristics of modern DBMS?
What security features are available for stored procedure?
What is Self Join?
Explain view in sql server?
What is the sql case statement used for? Explain with an example?
How to connect ms access to sql servers through odbc?
What are the ways available in sql server to execute sql statements?
Does partitioning help performance?
Please explain go command in sql server?
Characterize join and name diverse sorts of joins?