Write a query to get 2nd maximum salary in an employee table ?
Answers were Sorted based on User's Feedback
Answer / sandeep
select top(1) a.salary
from
(
select top(2)sal
from
employee
order by salary desc
)a
order by a.salary asc
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mark berlin.
REM solution #1:
select * from(
select distinct nvl(salary,-1) as sal
from employees
where nvl(salary,-1) < (select max(nvl(salary,-1)) from
employees)
order by nvl(salary,-1) desc)
where rownum=1;
REM Solution #2
select * from (
select distinct salary from employees
order by salary
desc
)
where rownum < 3
minus
select * from (
select distinct salary from employees
order by salary
desc
)
where rownum =1;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sushant hole
select max(salary) from employees where salary<(select max(salary) from
employees);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / boby
select max(MAXI) from
(SELECT max(slno) AS MAXI FROM EMPLOYEE WHERE slno not IN(SELECT max(slno) FROM EMPLOYEE )
GROUP BY slno)a
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / neha singh
select min(sal)
from
(select sal from
(select sal from emp
order by sal desc)
where rownum<=2)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / baburav zore
select max(sal) from employee where sal<(select sal from employee);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / akshay
Select ename, eno, sal, rownum
from (select ename, eno, sal from emp order by sal desc)
where rownum = 2;
Is This Answer Correct ? | 7 Yes | 7 No |
Answer / ahmad
Select Max(VacationHours) "MVacHrs"
from HumanResourcesEmployee
where VacationHours not in (Select Max(VacationHours)
from HumanResourcesEmployee)
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / google
Select min(sal) from emp where sal in (
select top 3 sal from emp
order by sal desc )
Is This Answer Correct ? | 0 Yes | 0 No |
what is meant by urlencode and urldocode? : Sql dba
Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin
Does mysql_real_escape_string prevent sql injection?
What is sql and db2?
What is sql profiler in oracle?
What is the difference between drop and truncate commands?
how to fetch common records from two tables? : Sql dba
Is sql a oracle?
Is json a nosql?
sales persons should always receive commission of 100 at least. employees who r not sales persons should never receive commission.(Triggers)
What if we write return in procedure?
How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate values eg: Name Sal Malli 60000 Pandi 60000 Rudra 45000 Ravi 45000
10 Answers Mind Tree, Polaris, Sonata,