How To Use DISTINCT With a Join?

Question

This is practice with the MySQL Employees DB. The diagram for it is here.

I want to know the top 10 salaries.salary, by employees.gender.

I have tried:

SELECT DISTINCT employees.emp_no, employees.first_name, employees.last_name, employees.gender, salaries.salary 
FROM employees 
JOIN salaries ON employees.emp_no = salaries.emp_no 
ORDER BY salaries.salary DESC 
LIMIT 10;

+--------+------------+-----------+--------+--------+
| emp_no | first_name | last_name | gender | salary |
+--------+------------+-----------+--------+--------+
|  43624 | Tokuyasu   | Pesch     | M      | 158220 |
|  43624 | Tokuyasu   | Pesch     | M      | 157821 |
| 254466 | Honesty    | Mukaidono | M      | 156286 |
|  47978 | Xiahua     | Whitcomb  | M      | 155709 |
| 253939 | Sanjai     | Luders    | M      | 155513 |
| 109334 | Tsutomu    | Alameldin | M      | 155377 |
| 109334 | Tsutomu    | Alameldin | M      | 155190 |
| 109334 | Tsutomu    | Alameldin | M      | 154888 |
| 109334 | Tsutomu    | Alameldin | M      | 154885 |
|  80823 | Willard    | Baca      | M      | 154459 |
+--------+------------+-----------+--------+--------+

It's almost right, but what happened with my DISTINCT? I don't want so see the same emp_no in the table.

`DISTINCT` applies to the entire `SELECT` list. Since the salaries are different, the rows aren't duplicates. — Barmar, Jul 25 '21 at 05:13
You should join with a subquery that just gets the most recent salary for each employy. You're getting multiple rows because you're showing their entire salary history. — Barmar, Jul 25 '21 at 05:15
What is the rule for which of the "duplicate" employee records to retain? — Tim Biegeleisen, Jul 25 '21 at 05:15
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1 for how to get the row the maximum date. — Barmar, Jul 25 '21 at 05:15
Just want to get the highest salary for each employee. So, see the top 10 without involving salary history - only top 10 with highest salary for each `emp_no`. — CodeFinity, Jul 25 '21 at 05:16
Then use `MAX(salary)` and `GROUP BY emp_no`. `GROUP BY` will make it distinct. — Barmar, Jul 25 '21 at 05:22
`SELECT DISTINCT employees.first_name, employees.last_name, employees.gender, MAX(salaries.salary) FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no GROUP BY employees.emp_no ORDER BY salaries.salary DESC LIMIT 10;` is what I have now. Getting error related to: `sql_mode=only_full_group_by`. — CodeFinity, Jul 25 '21 at 05:28

score 1 · Answer 1 · edited Jul 30 '21 at 06:16

SELECT employees.emp_no, employees.first_name, employees.last_name, employees.gender, 
       MAX(salaries.salary) AS salary 
FROM employees 
JOIN salaries ON employees.emp_no = salaries.emp_no 
GROUP BY employees.emp_no 
ORDER BY salary DESC 
LIMIT 10;

+--------+------------+-----------+--------+--------+
| emp_no | first_name | last_name | gender | salary |
+--------+------------+-----------+--------+--------+
|  43624 | Tokuyasu   | Pesch     | M      | 158220 |
| 254466 | Honesty    | Mukaidono | M      | 156286 |
|  47978 | Xiahua     | Whitcomb  | M      | 155709 |
| 253939 | Sanjai     | Luders    | M      | 155513 |
| 109334 | Tsutomu    | Alameldin | M      | 155377 |
|  80823 | Willard    | Baca      | M      | 154459 |
| 493158 | Lidong     | Meriste   | M      | 154376 |
| 205000 | Charmane   | Griswold  | M      | 153715 |
| 266526 | Weijing    | Chenoweth | F      | 152710 |
| 237542 | Weicheng   | Hatcliff  | F      | 152687 |
+--------+------------+-----------+--------+--------+

works!

Daniil Loban · Answer 2 · 2021-07-25T06:33:38.117

1

You can use aggregate functions to collapse extra lines

SELECT employees.emp_no, employees.first_name,
  employees.last_name, employees.gender, 
  AVG(salaries.salary) as salary
FROM employees 
JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY employees.emp_no 
ORDER BY salary DESC 
LIMIT 10

emp_no	first_name	last_name	gender	salary
43624	Tokuyasu	Pesch	M	158020.5000
254466	Honesty	Mukaidono	M	156286.0000
47978	Xiahua	Whitcomb	M	155709.0000
253939	Sanjai	Luders	M	155513.0000
109334	Tsutomu	Alameldin	M	155085.0000
80823	Willard	Baca	M	154459.0000

edited Jul 25 '21 at 06:33

answered Jul 25 '21 at 06:22

Daniil Loban

4,165
1
14
20

We can add `ROUND(AVG(salaries.salary, 2)` also. But, why are we doing `AVG`? Ur taking average of the salaries that they have ever earned instead of highest. It's a different result **technically,** but gives the same results, pretty much. . – CodeFinity Jul 25 '21 at 06:36
1

Thanks, of course you are right, but I added a link to functions to decide what need) – Daniil Loban Jul 25 '21 at 06:42

score 0 · Answer 3 · answered Jul 25 '21 at 07:39

For example:

SELECT e.*
     , s.salary 
  FROM employees e
  JOIN salaries s
    ON s.emp_no = e.emp_no 
  JOIN (SELECT emp_no,MAX(salary) salary FROM salaries GROUP BY emp_no) x
    ON x.emp_no = s.emp_no 
   AND x.salary = s.salary
 ORDER 
    BY s.salary DESC 
 LIMIT 10;

How To Use DISTINCT With a Join?

3 Answers3