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
Answer / your name
Forget everything...Here it goes..in a simple way...
Select salary from tbl_name a where
n = (Select count(distinct(count(*))) from tbl_name b
where b.salary>=a.salary)
Substitute you value for n. thats all.
Is This Answer Correct ? | 0 Yes | 1 No |
SELECT EmpId,Salary
FROM(SELECT ROW_NUMBER() OVER(ORDER BY Salary Desc) AS [ROW_NUMBER],* FROM (SELECT DISTINCT Salary,Empid FROM EMPDetails)Emp)Emp1
WHERE ROW_NUMBER BETWEEN 2 AND 5
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / prasant palo
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
Is This Answer Correct ? | 2 Yes | 5 No |
Answer / prashant narvekar
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)
Is This Answer Correct ? | 2 Yes | 5 No |
Answer / savita vishwakarma
select max(sal)
from (select rownum
from emp
order by sal desc)
where rownum=2
Is This Answer Correct ? | 2 Yes | 5 No |
Answer / vishakha shrivastava
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)
Is This Answer Correct ? | 2 Yes | 5 No |
Answer / vinay singh
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
Is This Answer Correct ? | 8 Yes | 12 No |
Answer / balaji
TO FIND THE N-1th HIGHEST SALARY IN EMP TABLE
Select Min(salary) from emptable where salary not in(Select
Min(salary) from emptable)
Is This Answer Correct ? | 0 Yes | 4 No |
Answer / subhranghshu bhattacharjee
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
Is This Answer Correct ? | 4 Yes | 9 No |
How to create nested stored procedure?
Where sql server usernames and passwords are stored in a sql server?
What is checkpoint process in the sql server?
How to select top 5 coloumn from a table without using coloumn name
What is best institute to Learn DotNET And SQL in chennai?
What is impersonation? What are the different impersonation options available in ssas? : sql server analysis services, ssas
Explain nested trigger in sql?
Working with TLogs
we have emp table like Ename,EDOJ,EDOB with Column structure.but we want to know the employee Age.How? Any Body Plz
What is policy management?
what is a traditional network library for sql servers? : Sql server database administration
How to select some specific columns from a table in a query in ms sql server?