CODEWITHSUNDEEP
sql
kalyani chowdary
kalyani chowdary

Reputation: 3

Need Max values from the query?

I have two tables

Employee ( empid integer, mgrid integer, deptid integer, salary integer)

Dept (deptid integer, deptname text),

I am trying find to departments that have maximum number of employees. I tried this but i am getting all the dept Id's, how to get dept Id that has maximum employees.

SELECT dept.DeptName,
    COUNT(emp.EmpId) AS NUM_OF_EMPLOYEES
  FROM Dept dept
  INNER JOIN Employee emp
  ON dept.DeptId = emp.deptId
  GROUP BY dept.DeptName;

Upvotes: 0

Views: 61

Answers (1)

JNevill
JNevill

Reputation: 50218

It depends on your DB, but the logic is the same. You want to sort the results, then just grab the top of the results.

In sql-server, Access, Teradata and a few other DB's you would use TOP:

SELECT TOP 1 dept.DeptName,
    COUNT(emp.EmpId) AS NUM_OF_EMPLOYEES
  FROM Dept dept
  INNER JOIN Employee emp
  ON dept.DeptId = emp.deptId
  GROUP BY dept.DeptName
  ORDER BY NUM_OF_EMPLOYEES DESC

On MySQL or Postgres you would use LIMIT:

  SELECT dept.DeptName,
    COUNT(emp.EmpId) AS NUM_OF_EMPLOYEES
  FROM Dept dept
  INNER JOIN Employee emp
  ON dept.DeptId = emp.deptId
  GROUP BY dept.DeptName
  ORDER BY NUM_OF_EMPLOYEES DESC
  LIMIT 1

In Oracle you use RowNum:

  SELECT dept.DeptName,
    COUNT(emp.EmpId) AS NUM_OF_EMPLOYEES
  FROM Dept dept
  INNER JOIN Employee emp
  ON dept.DeptId = emp.deptId
  WHERE ROWNUM=1
  GROUP BY dept.DeptName
  ORDER BY NUM_OF_EMPLOYEES DESC

Upvotes: 1

Related Questions