Oracle SQL Query: Getting the Biggest Sale for Employee

Question

I want to find the biggest sale for each of my employees (and display the name of the employee). In MySQL, this is pretty simple:

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

This is pretty much what you would expect, it will return a list of employees and eventually return the largest sales record with a number of employees.

But Oracle does not allow you to return columns that are not group expressions when using the group by clause. Does this do what I do in MySQL "impossible" in Oracle? Or is there some workaround? I suppose I could do some kind of subquery, but I'm not sure if there is another way to do this, which is not so difficult to build.

Answer 1

Get rid of select *and replace it with just the columns you want, and then group all the columns without processing.

You 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

This is a pain - I had to do it many times before - but just add all the related columns to your group

Answer 2

To get the biggest sale, you can use the group using the function max.

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 made the assumption that the name of the employee is in the column of the nametable employee. I also smoothed employee tables and sales tables.
• If you prefer to see the total sales for an employee, you can replace max()and use sum()instead.

Answer 3

, , !

, , - . , desc, MySQL, ANSI SQL. ( , MySQL , "" - .)

- , , ; , : . SQL :

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

Answer 4

, GROUP BY.

, , 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 one row with a total maximum sale.

If you want to choose the highest sale per employee, simply add the offer PARTITION BYto your request:

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