-2

enter image description hereenter image description here

SELECT DISTINCT 
  employees.departmentname, 
  employees.firstname, 
  employees.salary, 
  employees.departmentid
FROM employees
JOIN (
  SELECT MAX(salary) AS Highest, departmentID 
  FROM employees 
  GROUP BY departmentID
) departments ON employees.departmentid = departments.departmentid 
             AND employees.salary = departments.highest;

Why doesn't the DISTINCT work here? I'm trying to have each department to show only once because the question is asking the highest salary in each department.

MT0
  • 143,790
  • 11
  • 59
  • 117
Jay Li
  • 1
  • 1
  • 4
    The `DISTINCT` keyword remove "whole duplicate rows". As you see there are no duplicate rows in your example. – The Impaler Apr 24 '21 at 03:25
  • the departmentname and departmentid are duplicates – Jay Li Apr 24 '21 at 03:32
  • @Asenar . . . Your comment is just wrong and you should remove it. – Gordon Linoff Apr 24 '21 at 12:19
  • Ok @GordonLinoff I just did, but are you sure ? I don't know Oracle a lot, but I quickly searched (after your comment) and it seems the author might want to use `UNIQUE` instead of `DISTINCT`, https://sql.sh/cours/distinct – Asenar Apr 24 '21 at 12:24

4 Answers4

1

Use the ROW_NUMBER() function, as in:

select departmentname, firstname, salary, departmentid
from (
  select e.*,
    row_number() over(partition by departmentid, order by salary desc) as rn
  from employees e
) x
where rn = 1
The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

I'm trying to have each department to show only once because the question is asking the highest salary in each department.

Use window functions:

SELECT e.*
FROM (SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY departmentID ORDER BY salary DESC) as seqnum
      FROM employees e
     ) e
WHERE seqnum = 1;

This is guaranteed to return one row per department, even when there are ties. If you want all rows when there are ties, use RANK() instead.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i typed in every word but it returned an error, I posted the picture above. – Jay Li Apr 24 '21 at 03:56
  • 1
    @JayLi No, you missed the `seqnum` alias after the `as` keyword. It looks like you typed what you could see and did not move the scroll bar to see the rest of the text that has overflowed at the right of the answer. – MT0 Apr 24 '21 at 08:58
0

Why doesn't the DISTINCT work here?

DISTINCT is not a function; it is a keyword that will eliminate duplicate rows when ALL the column values are duplicates. It does NOT apply to a single column.

The DISTINCT keyword has "worked" (i.e. done what it is intended to do) because there are no rows where all the column values are a duplicate of another row's values.

However, it hasn't solved your problem because DISTINCT is not the correct solution to your problem. For that, you want to "fetch the row which has the max value for a column [within each group]" (as per this question).

MT0
  • 143,790
  • 11
  • 59
  • 117
-1

Gwen, Elena and Paula all have the same salary

and they are in the same department

wavery
  • 269
  • 1
  • 5