write a sql query for below condition?

Question

find out the 2nd most no. of employee working with dname from emp and dept table.

select d.dname,count(*) "totemp" from emp e,dept d
where e.deptno=d.deptno
group by (d.dname)
having count(*)=(select max(count(*)) from emp e,dept d 
where e.deptno=d.deptno
group by d.dname);

This query will show highest no. of employee working with dname. But i want 2nd highest with dname.

score 0 · Answer 1 · answered Oct 29 '17 at 00:00

0

First use grouping to count employees for each dept, then use dense_rank() window function to assign rank values for dept groups and lastly select the dept which is ranked second.

select dname
from (
  select dname, dense_rank() over (order by cnt desc) as rnk
  from (
    select d.dname, count(*) as cnt
    from emp e
    join dept d using (deptno)
    group by d.dname
  ) t
) x
where rnk = 2

Using dense_rank() function you resolve ties properly, so if there are more than one depts with the same number of employees they will all pop up.

You can change your solution using row_number() function to pick any arbitrary dept even if there are ties on second position.

answered Oct 29 '17 at 00:00

Kamil Gosciminski

16,547
8
49
72

But here only dname is shown but i want dname with total no. of employee – saroj kumar Oct 30 '17 at 18:50
add `cnt` column and it's all there. – Kamil Gosciminski Nov 01 '17 at 02:09
union vs union all which gives more performance and why> – saroj kumar Nov 01 '17 at 05:15
accept the answer if it helped you, Here's how: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Kamil Gosciminski Nov 01 '17 at 14:05
Difference between UNION and UNION ALL is well described over the net – Kamil Gosciminski Nov 01 '17 at 14:05

write a sql query for below condition?

1 Answers1