# Find the Max value of different fields in a row

Dear Experts,

I have a table that has 5 columns for balances of 5 different accounts a customer holds. So the table looks as follows

CustID     Acct1     Bal1     Acct2     Bal2     Acct3     Bal3     Acct4     Bal4     Acct5     Bal5

I need to write a query that returns all the custID's and the maximum balance of all the accounts. The resultset should look as follows

CustID     MAX_Balance

I know that MAX cannot be used in this case as it finds the max within a column. I need to find the max within a row. How can this be done? Please help.

Thanks

###### Who is Participating?

Commented:
ok, from what I gather, u want the value of the highest balance?

CustID     Acct1     Bal1     Acct2     Bal2     Acct3     Bal3     Acct4     Bal4     Acct5     Bal5

public function getMaxBalance(Bal1,Bal2,Bal3,Bal4,Bal5) as double
getMaxBalance = Bal1
if Bal2 > getMaxBalance then getMaxBalance = Bal2
if Bal3 > getMaxBalance then getMaxBalance = Bal3
if Bal4 > getMaxBalance then getMaxBalance = Bal4
if Bal5 > getMaxBalance then getMaxBalance = Bal5
end function

Now in your query, u can do this

SELECT CustID,getMaxBalance(NZ(Ba1,0),NZ(Bal2,0),NZ(Bal3,0),NZ(Bal4,0),NZ(Bal5,0)) as MaxBalance
from mytable

note I used NZ so as to handle nulls
0

Commented:
Wouldnt your table definiton be more suited to be like this

tblCustomer
CustID
Name
etc

tblAccounts
AcctID
CustID
Balance

Now this is a one to many, u can have as many accounts per customer as u like and it can vary

all u now need to do is

select CustID, Max(Balance)
FROM tbLCustomer, tblAccounts
WHERE tblCustomer.CustID = tblAccounts.CustID
GROUP BY CustID

I believe your table definiton is wrong. What if u decide to have 6 accounts, think if the work involved, u have to change table defintion, your queries etc
using a table like I suggested, its merely adding another row

u can then extend tblAccounts to include account type etc
0

Commented:
bedtime, night all
0

Author Commented:

thanks for the suggestion. I know this is how it should be done and seems sensible. But I've just taken this application over from a demoniac programmer who has now been sacked for his animalistic logic.

Unfortunately, we cannot change the table design since people have been using it for years. I have to work this design and come up with a solution.

0

Author Commented:
Thanks for the solution. Works great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.