CareerCup

select max(sal) from emp
union
select max(sal) from emp where sal not in (select max(sal) from emp)

use the sql top command ...

one has to write a recursive query first find out the max(M1) ,then again find out the max such that max!= M1.implementation may vary from rdbms used but i guess logic is some what same

Select Top 2 Salary
From Salary_Table
Order BY Salary DESC;

This works !

select * from emp_tab where ( select count(*) from emp_tab st where st.sal < emp_tab.sal ) <= n ; ( here n is 2 )

select * from emp_tab where ( select count(*) from emp_tab st where st.sal < emp_tab.sal ) <= n ; ( here n is 2 )

I am not that familiar with writing complex sql queries,

but

1) Recursive queries?
2) select from another select?

When we have the 'top' command, which is highly likely to have been optimized by the Database, why does one have to go and try to come up with silly queries which are likely to be inefficient?

Correctness is not the only virtue...

I completely agree with LOLer's logic!

select * from emp e1 where ( select count(*) from emp e2 where e2.sal>e1.sal and e1.grp=e2.grp) < n

This query gives top 'n' salaries of each group..

The "top" and other such things are non standard constructs which are specific to a DBMS..

select sal, deptno from
(select sal, t.deptno, rank() over (partition by deptno order by sal desc) r
from scott.emp t) 
where r < 3

Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

Assume salary table only has one column salary:

select t0.salary, count(t1.salary) rank
from
(select distinct salary from salary) t0
left outer join
(select distinct salary from salary) t1
on t0.salary < t1.salary
group by t0.salary
having rank < 2
order by t0.salary desc

select distinct salary
from emp e1
where 2 >= (select count(distinct(salary)) from emp2 where e1.salary<=e2.salary)
order by salary DESC);

Amit's solution works

;with ctetst
as
(
SELECT
Grp, Salary, ROW_NUMBER() OVER(PARTITION BY Grp ORDER BY Salary DESC) as rowss
FROM GrpTbl
)

SELECT
Grp, Salary, (CASE WHEN rowss = 1 THEN 'Highest Salary' ELSE 'Second Highest' END) as salaryLevel
FROM ctetst
WHERE rowss IN (1, 2)

SELECT e1.empname, e1.`deptid`, e1.salary, COUNT(e2.empid)
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.`salary` < e2.salary
AND e1.`deptid` = e2.`deptid`
GROUP BY e1.`empname`, e1.`deptid`, e1.salary
HAVING COUNT(e2.empid)  < 2