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 |
What is the difference between executequery () and executeupdate ()?
What is use of attributehierarchyenabled? : sql server analysis services, ssas
Is sql server implemented as a service or an application? : Sql server database administration
How to insert a new row into a table with "insert into" statements in ms sql server?
What is the difference between ddl,dml and dcl commands?
0 Answers BirlaSoft, Verifone,
What is indexing and its types?
What is Pointer ?
3 Answers Cap Gemini, CarrizalSoft Technologies,
i have a table student like sname ----- ram ram ram raj raj raj i need the output like sname ----- ram raj ram raj ram raj
How sql server enhances scalability of the database system?
What options are available to audit login activity? : sql server security
Why can there be only one clustered index and not more than one?
What is history table in sql server?