CODEWITHSUNDEEP
sqloracle-databasesubquery
Jorge Nitales
Jorge Nitales

Reputation: 159

Getting last value of a grouped result - Oracle SQL

Having a list of bills I want to know the sales volume of every customer grouping by id. Every bill has a responsible Person (a shop assistant who is either in the table customer), so I want to pick up the person of the last bill and have it in the result.

Result should be something like this: Customer - Sum of the bills - Responsible Person of the last bill.

As Oracle has no "Limit" option (we don´t have 12c yet) I have to do it with a Subselect. It means that I can´t compare the ID of the customer because it´s out of the scope (see Code below). Any recommendation will be welcome.

SELECT customer.NAME, SUM(bills.SUME), 
(SELECT responsibleP FROM 
    (SELECT responsibleP FROM bills b WHERE b.responsibleP = customer.id 
    order by asc billDate)
WHERE ROWNUM = 1)
FROM customer, bills
WHERE customer.id = bills.customerid    
GROUP BY customer.id;

In this case b.responsible is not able to get the customer.id from the customer table.

Please, feel free to change the Title, I´m not really nice at that, sorry!

Upvotes: 3

Views: 7519

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

In Oracle use KEEP LAST:

SELECT
  c.name, 
  SUM(bills.sume), 
  MAX(b.responsibleP) KEEP (DENSE_RANK LAST ORDER BY b.billDate)
FROM customer c
JOIN bills b ON c.id = b.customerid    
GROUP BY c.id;

Upvotes: 0

MT0
MT0

Reputation: 167774

You should be able to use MAX( column ) KEEP ( DENSE_RANK [FIRST|LAST] ORDER BY other_columns [ASC|DESC] ) to get the maximum of one column that also has the FIRST or LAST value of another column. Like this:

SELECT c.NAME,
       SUM(b.SUME),
       MAX( b.respondibleP ) KEEP ( DENSE_RANK LAST ORDER BY b.billDate )
         AS lastResponsible
FROM customer c
     INNER JOIN bills b
     ON ( c.id = b.customerid )
GROUP BY c.id;

Upvotes: 11

Related Questions