sql sub query

Question

Hi I need a small help regarding writing a simple query. I want to display results based on max number of employees in a dept.

Here is my table

empid           dept           sal
emp001          d001           10000
emp002          d001           10000
emp003          d002           20000
emp004          d001           10000
emp005          d003            5000
emp006          d003            5000

Expected result

empid         dept           sal
emp001         d001           10000
emp002         d001           10000
emp004         d001           10000
emp005         d003            5000
emp006         d003            5000
emp003         d002           20000

so dept1 contains 3 employees so it should come first and dept3 contains 2 employees so it will come next and so on

Thanks in advance,

Nagu

Answer 1

I am not sure what you want exactly, but this query gives you the number of employees per dept.

SELECT dept, COUNT(*) FROM table GROUP BY dept

Best Regards

Answer 2

You could calculate the headcount per department in a join, and then order by that:

select e.empid, e.dept e.sal
from employees e
inner join (
    select dept, total = count(*)
    from employees
    group by dept
) headcount hc on hc.dept = e.dept
order by hc.total desc, e.dept, e.sal

Answer 3

The answer hardly depends on your DBMS ! With Oracle 8+ you can use analytic functions :

select empid, dept, sal
from MyTable
order by count(empid) over (partition by dept) desc

Answer 4

Just tried this in MySQL, and with this query:

SELECT e1. * , count( e2.empid ) AS c FROM employees e1 LEFT JOIN employees e2 ON e1.dept = e2.dept GROUP BY e1.empid ORDER BY c DESC

I got this result:

empid   dept  sal  c
emp001  d001  10000  3
emp004  d001  10000  3
emp002  d001  10000  3
emp006  d003  5000  2
emp005  d003  5000  2
emp003  d002  20000  1

And then you could of course sort on empid to get the emp002 before the emp004 etc :)

Edit: A better MySQL query would be with not selecting * elements and escaping all table and field names with backticks, something like this:

SELECT \e1\.\empid\, \e1\.\dept\, \e1\.\sal\, COUNT(\e2\.\empid\) AS \c\ FROM \employees\ \e1\ LEFT JOIN \employees\ \e2\ ON \e1\.\dept\ = \e2\.\dept\ GROUP BY \e1\.\empid\ ORDER BY \c\ DESC, \e1\.\empid\

Answer 5

The answer hardly depends on your DBMS ! With Oracle 8+ you can use analytic functions :

The same query works with SQL Server 2005+

There is another solution that gives same result and almost the same execution plan:

;With DeptCount as 
( Select dept, count(empid) as cnt
    from employees
   group by dept
)
Select e.empid, e.dept e.sal
  From employees e
 Inner Join DeptCount c  
         on e.dept=c.dept
 Order By c.cnt DESC, e.empid ASC

Answer 6

Version without CTE:

Select e.empid, e.dept e.sal
  From employees e
 Inner Join ( Select dept, count(empid) as cnt
                from employees
               group by dept
            )  c on e.dept=c.dept
 Order By c.cnt DESC, e.empid ASC