Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Thumbs up Unanswered: [SOLVED] List the employees of the department with the most workers

    Hello world!
    I have been thinking about a rather simple question for some time now, but unfortunately I could not come up with an answer. I am using MySQL 5.1, and the database is "the" employees database (click here to see the database structure).
    As the title says, I would like to list the employees of a specific department - the department with the most employees on a given date. So? Anyone out there, with a neat query up his/her sleeve?
    Last edited by szlraci; 07-20-11 at 12:13.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when is this assignment due?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    3
    Hello there! I am to come up with a solution until thursday. I do not wish you to take your time though, I was thinking there may be people who can throw in a quick solution and maybe some comments too about how and why is it working. But ofcourse, if you have the time and the inclination, I shall be thankful.

  4. #4
    Join Date
    Jul 2011
    Posts
    3
    I have managed to find a solution for the problem. If anyone has a more optimal solution, he/she is welcome post it! My query is:

    Code:
    /* 4. */
    SELECT first_name, last_name
    FROM employees
    JOIN dept_emp ON dept_emp.emp_no=employees.emp_no
    WHERE dept_no=(
    	SELECT max_emps_tbl.dept_no
    	FROM (
    		/*  3. */
    		SELECT departments.dept_no, COUNT(*) AS nr
    		FROM departments
    		JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
    		GROUP BY dept_no
    		HAVING nr=(
    			/* 2. retrieves the maximum value of the nr_of_emps column */
    			SELECT MAX(dept_emp_summary.nr_of_emps) AS max_emps
    			FROM (
    				/* 1.: creates a summary of departments and the number of employees*/
    				SELECT departments.dept_no, COUNT(*) AS nr_of_emps
    				FROM departments
    				JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
    				GROUP BY dept_no)
    			AS dept_emp_summary))
    	AS max_emps_tbl
    )

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •