How to select the latest salary under some other conditions? MySQL

Question

I want to select just the latest salary of senior, female employees. I have the following query by now:

SELECT 
    e.emp_no,
    e.gender,
    tit.title,
    s.* 

FROM employees e
INNER JOIN titles tit ON tit.emp_no = e.emp_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.gender = "F"
AND tit.title LIKE '%Senior%'

which gives the following result:

gender  title               emp_no  salary  from_date       to_date
"F"     "Senior Engineer"   "10006" "40000" "1990-08-05"    "1991-08-05"
"F"     "Senior Engineer"   "10006" "42085" "1991-08-05"    "1992-08-04"
"F"     "Senior Engineer"   "10006" "42629" "1992-08-04"    "1993-08-04"
"F"     "Senior Engineer"   "10006" "45844" "1993-08-04"    "1994-08-04"
"F"     "Senior Engineer"   "10006" "47518" "1994-08-04"    "1995-08-04"
"F"     "Senior Engineer"   "10006" "47917" "1995-08-04"    "1996-08-03"
"F"     "Senior Engineer"   "10006" "52255" "1996-08-03"    "1997-08-03"
"F"     "Senior Engineer"   "10006" "53747" "1997-08-03"    "1998-08-03"
"F"     "Senior Engineer"   "10006" "56032" "1998-08-03"    "1999-08-03"
"F"     "Senior Engineer"   "10006" "58299" "1999-08-03"    "2000-08-02"
"F"     "Senior Engineer"   "10006" "60098" "2000-08-02"    "2001-08-02"
"F"     "Senior Engineer"   "10006" "59755" "2001-08-02"    "9999-01-01"
"F"     "Senior Staff"      "10007" "56724" "1989-02-10"    "1990-02-10"
"F"     "Senior Staff"      "10007" "60740" "1990-02-10"    "1991-02-10"
"F"     "Senior Staff"      "10007" "62745" "1991-02-10"    "1992-02-10"

What am I missing here?

What you are missing is your query doesn't have the statement to select the latest salary. — Eric, Nov 12 '19 at 19:29
Obviously an employee can have multiple titles, hence the separate table, right? Isn't there something more that relates titles and salaries? A date span in the titles table maybe? If not, which title would you show in case an employee is/was both 'Senior Engineer' and 'Senior Staff'? — Thorsten Kettner, Nov 12 '19 at 19:38
@ThorstenKettner the only relation between titles and salaries is _emp_no_ . I guess we may not print the titles at all. My sql is version 3.6.3. — big_OS, Nov 12 '19 at 19:46
Missing: https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Nov 12 '19 at 19:47
That's a very old version. Current MySQL version is 8.0.18. As to titles: so how to interpret the data? Does the table only contain current titles? Or does it really contain all titles an employee ever held? If so, how then to find the current one? What would be the table's use? — Thorsten Kettner, Nov 12 '19 at 19:49
@ThorstenKettner wait.. I messed up with mongodb.. mysql is 5.7.27 but i guess its kind of old as well.. The current title has _to_date_ as _9999-01-01_ and yes, the table stores every title ever held — big_OS, Nov 12 '19 at 19:55
No, don't worry, 5.7.2.7 was followed directly by 8.0. Anyway, version 8 has great features finally added to MySQL (namely: analytic window functions), that help writing better, less clumsy queries. — Thorsten Kettner, Nov 12 '19 at 19:59

ScaisEdge · Answer 1 · 2019-11-12T19:39:52.783

If you want only the latest you could use order by to_date and limit 1

SELECT 
    e.emp_no,
    e.gender,
    tit.title,
    s.* 

FROM employees e
INNER JOIN titles tit ON tit.emp_no = e.emp_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.gender = "F"
AND tit.title LIKE '%Senior%'
order by to_date 
limit 1

and for each emp_no ypu could use a join with subqiery formax(to_date)

  SELECT 
      e.emp_no,
      e.gender,
      tit.title,
      s.* 

  FROM employees e
  INNER JOIN titles tit ON tit.emp_no = e.emp_no
  INNER JOIN salaries s ON e.emp_no = s.emp_no
  INNER JOIN  (
          SELECT 
          e.emp_no,
          max(s.to_date) max_date 
      FROM employees e
      INNER JOIN titles tit ON tit.emp_no = e.emp_no
      INNER JOIN salaries s ON e.emp_no = s.emp_no
      WHERE e.gender = "F"
      AND tit.title LIKE '%Senior%'
      GROUP BY e.emp_no 
  ) t on t.emp_no  = e.emp_no 
      AND s.to_date = t.max_date
  WHERE e.gender = "F"
  AND tit.title LIKE '%Senior%'

I mean for _emp_no=10006_ should be _59755_ , for _emp_no=10007 - 62745_ and so on — big_OS, Nov 12 '19 at 19:38

score 0 · Accepted Answer · answered Nov 12 '19 at 19:45

You are missing the condition to only show the last salary. One way to express this is to demand there exists no later salary for the employee:

SELECT 
  e.emp_no,
  e.gender,
  tit.title,
  s.* 
FROM employees e
INNER JOIN titles tit ON tit.emp_no = e.emp_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.gender = 'F'
AND tit.title LIKE '%Senior%'
AND NOT EXISTS
(
  SELECT *
  FROM salaries s2
  WHERE s2.emp_no = s.emp_no
  AND s2.from_date > s.from_date
);

Or select all latest dates and use them:

...
AND tit.title LIKE '%Senior%'
AND (emp_no, from_date) IN
(
  SELECT emp_no, MAX(from_date)
  FROM salaries
  GROUP BY emp_no
);

How to select the latest salary under some other conditions? MySQL

2 Answers2