Oracle SQL Query: Getting Largest Sale for Employee

Question

I want to find the largest sale for each of my employees (and display the name of the employee). In MySQL, it's pretty straightforward:

select * 
  from employee, sale 
  where employee.id = sale.employee_id 
  group by employee_id 
  order by sale.total desc

This does pretty much what one would expect, it would return a list of employees and end up returning the largest sale record with the employee row.

But, Oracle does not allow you to return columns which are not group by expressions when a group by clause is used. Do this make what I do in MySQL "impossible" in Oracle? Or is there some workaround? I suppose I could perform some sort of subquery, but not sure if there is another way to do this that wouldn't quite be so complicated to construct.

Answer 1

To get the largest sale you can use group by with the max function.

select e.name, max (s.total) 
  from employee e, sale s 
  where e.id = s.employee_id 
  group by e.name 
  order by s.total desc

• I have made an assumption that the employee name is in the name column of the employee table. I have also aliased the employee table and sales tables.
• If you would prefer to see the total sales for an employee, you can swap out max() and use sum() instead.

Answer 2

Get rid of your select * and replace it with just the columns you need and then group by all the "non-processed" columns.

You'll end up with something like:

select employee.id, employee.name, max(sale.total)
from employee, sale
where employee.id = sale.employee_id
group by employee.id, employee.name
order by max(sale.total) desc

It's a pain - I've had to do this many times before - but just add all the related columns to your group by

Answer 3

Congratulations, you've learned just enough to be dangerous!

What you really want is each employee's largest sale. Now it happens that sorting them by sales amount desc and then grouping them works in MySQL, even though that isn't legal according to ANSI SQL. (Basically, MySQL is arbitrarily grabbing the first row for each employee, and that "works" because of the sort.)

The right way to do this is not to rely on the side of effect of the sort doing what you want; instead you should explicitly ask for what you want: the largest sale for each employee. In SQL that's:

select employee.id, max( sale.total) 
from employee, sale 
where employee.id = sale.employee_id 
group by employee.id 
order by 2

Answer 4

If you want to select an employee with a highest sale, you don't need GROUP BY here at all.

All you need is to select the highest sale and join it back to the employees:

SELECT  *
FROM    (
        SELECT  sale.*, ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
        FROM    sale
        ) s
JOIN    employee e
ON      e.id = s.employee_id
        AND s.rn = 1

This will select a single row with a total highest sale.

If you want to select per-employee highest sale, just add a PARTITION BY clause to your query:

SELECT  *
FROM    (
        SELECT  sale.*, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY total DESC) AS rn
        FROM    sale
        ) s
JOIN    employee e
ON      e.id = s.employee_id
        AND s.rn = 1