Igorles Igorles - 5 months ago 25
SQL Question

sql oracle max(sum)

I am trying to write a query in Oracle which will return max value. I have 3 tables Employee, customer , orders.

SELECT a.ID_Employee, a.NAME, a.SURNAME, SUM(c.value) AS "value" FROM Employee a, CUSTOMER b, ORDERS c WHERE a.ID_Employee = b.ID_Employee and b.ID_CUSTOMER = c.ID_CUSTOMER group by a.ID_Employee, a.NAME, a.SURNAME ;


The result of questions :

id_Employess Name Surname Value
7654 KENNETH MARTIN 27348
7555 DANIEL PETERS 11060
7820 PAUL ROSS 20796,8
7844 MARY TURNER 58055,9
7560 SARAH DUNCAN 36191,2
7521 CYNTHIA WARD 9984,2
7499 KEVIN ALLEN 7870,8
7789 LIVIA WEST 32274,2
7557 KAREN SHAW 33655,65


And i need max value from this result should be :

id_Employess Name Surname Value
7844 MARY TURNER 58055,9

Answer

just use rownum after order by

  select * from (
        select ID_Employee, NAME, SURNAME, value, rownum rn
         from (
        SELECT a.ID_Employee, a.NAME, a.SURNAME, SUM(c.value) AS "value"  
          FROM Employee a, CUSTOMER b, ORDERS c 
         WHERE a.ID_Employee = b.ID_Employee and b.ID_CUSTOMER = c.ID_CUSTOMER  
         group by a.ID_Employee, a.NAME, a.SURNAME 
         order by 4 desc
        )
    ) where rn = 1