How can I get first maximum salary as well as second maximum salary thru the same SQL query?
Printable View
How can I get first maximum salary as well as second maximum salary thru the same SQL query?
You can get it. But tell me you want this in single record or in two records?
By using below query u can retrieve the 1st & 2nd maximum salaries select sal from(select sal,rownum from emp group by sal,rownum order by sal desc) group by sal, having rownum=1 or rownum=2 order by sal desc; ok. I think u got it.
select top 2 salary from table_name order by salary desc
select (select max(sal) from tbl_employee), max(sal)from tbl_employee
where sal < (select max(sal) from tbl_employee)
select top 1 * from <table_name> having salary < max(salary) order by salary desc
[QUOTE=amarmail_999;14398]How can I get first maximum salary as well as second maximum salary thru the same SQL query?[/QUOTE]
select sal from emp e where &n=select count(sal) from (select max(sal) from emp f )where e.sal<=f.sal
Try this ..
Select empno, ename, sal,
DECODE (ROWNUM,
1, ROWNUM || 'st Highest Salary',
2, ROWNUM || 'nd Highest Salary'
) remark
From (Select *
From emp e
Order By sal Desc)
Where ROWNUM <= 2