ORACLE SQL I am having problem with a select statement that should show the MAX balance for every branch

Question

Hi guys and thanks to everyone will help me.

I have this query:

SELECT  c.accNum.bID.bID AS "Branch ID",
--c.accNum.accNumber AS "Account Number",
--c.accHolder.PrintFullName() AS "Customer Name",
MAX(c.accNum.balance) AS "Balance"
FROM tabCustomersAccounts c
GROUP BY c.accNum.bID.bID--, c.accNum.accNumber, c.accHolder.PrintFullName(), c.accNum.balance
ORDER BY c.accNum.bID.bID;

Results

When I change the code to this:

SELECT  c.accNum.bID.bID AS "Branch ID",
c.accNum.accNumber AS "Account Number",
c.accHolder.PrintFullName() AS "Customer Name",
MAX(c.accNum.balance) AS "Balance"
FROM tabCustomersAccounts c
GROUP BY c.accNum.bID.bID, c.accNum.accNumber, c.accHolder.PrintFullName(), c.accNum.balance
ORDER BY c.accNum.bID.bID;

Results

From this results you can see that I they are wrong because I want only the MAX balance to be displayed for each Branch.

Answer 1

Use row_number(). Your actual column names are a bit unclear, but something like this:

SELECT ca.*
FROM (SELECT ca.*,
             ROW_NUMBER() OVER (PARTITION BY ca.bid ORDER BY c.balance DESC) as seqnum
      FROM tabCustomersAccounts ca
     ) ca
WHERE seqnum = 1;