2

I have the following database table

Firstname  Varchar(40)
Surname    Varchar(40)
Department Binary
JobCode    Char(1)

Normally, pulling the records of a particular department is no problem. The department field is a 7-part field, with each bit representing a specific department, allowing the system to easily show where people work over one or more departments. Lets say that John Smith has a job code of K and Freda Williams has a Job Code of M.

Again, returning the records for a specific department is fine and easy. However, if John Smith has a temporary promotion to the same department, rather than change the record, an additional record is added with a Job Code of F. In essence the lower the job code letter the higher the grade.

So, if we want to retrieve John Smith's current details, it's simple, select the record where the name is John Smith, ordered by Job Code Asc, Limit (1).

But what is the SQL statement to pull all records for a department, but making sure only one record appears for John Smith and it is the 'F' record? Can anyone help? I had thought that a sub-query might sort it, but couldn't work out how.

5

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution.

You have a single table that is crammed by several design entities. While this is not bad per se, you must be aware that you are denormalizing your table design, and that can lead to problems.

I see 3 concepts on the same table:

  • Employee intrinsic details (First name, Family name)
  • Department membership
  • Job position holding by the employee within the department

According to your description, an employee can be part of several departments (and vice-versa) and you are implementing that as a bit field. I find interesting that you have chosen a binary data type, when a bit or a set data types would be potentially smaller and more legible respectively. However, those data types are usually avoided, not only because they are not very standard, but because they are used to denormalize columns.

Assuming there is a departments table, a better option would be to create a separte table: DepartmentEmployees, like this

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)
JobCode    Char(1)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?":

SELECT E.*
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

Now let's have a look at JobCode. You have to ask yourself if a Job Position is a property of the employee or of the employee within a department. If the latter, please consider adding that property there:

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
JobCode Char(1)
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?", with its Job Code:

SELECT E.*, DE.JobCode
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

This way you can get the JobCode, which can be different for each employee-department tuple (if needed).

You can consider creating (if they do not exist already), separate tables for Department, Jobs (as in, positions, for JobCode) and add unique Primary key everywhere.

In general, creating an intermediate table whenever there is an N:N relationship is a good option, unless there is a reason to denormalize the relationship (leading to more complex code).

Edit: The problem you are facing is horrible from all points- aside from the normalization problems, adding one extra record makes no sense. But here is the wanted solution:

Knowing there is nothing you can do about it, you are lucky because it is a very common query- that not only has a name "GROUPWISE MAXIMUM", it even has its own page on the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

There is both a subquery and a JOIN solution, I usually like the JOIN solution more, it would go something like this:

SELECT E1.FirstName, E1.Surname, E1.Department, s1.JobCode
FROM Employees E1
LEFT JOIN Employees E2
ON E1.FirstName = E2.FirstName AND E1.Surname = E2.Surname -- use an id for the SELF JOIN if possible, whatever is unique
AND E1.JobCode < E2.JobCode -- change the direction of the comparison depending if you want the lowest or the highest JobCode
WHERE E2.FirstName IS NULL  -- again, use the id if available
  AND E1.Department = ...;  -- or whatever the department filter should look like in your current setup

Allow me to shower after writing such a solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.