Tom Tom - 1 year ago 64
SQL Question

Find highest value in column using ANY or ALL

I'm new to SQL and am currently learning and this is probably a fairly basic question:

I have 4 tables

1) Customer (customerName, street, customerCity)

2) Deposit (customerName, branchName, accountNumber, balance)

3) Loan (customerName, branchName, loanNumber, amount)

4) Branch (branchName, branchCity, assets)

Each table has some data inserted into it.

I have been asked to find the customerName with the highest deposit amount. (So I'm guessing I will be using just the Deposit table)?

However, here is the catch, the sheet I am learning from is requesting that I MUST use either ALL or ANY, I can't simply use the MAX function to achieve this.

How would I achieve this? I've tried query after query and simply can't find a way to get it to work (baring in mind that I've only been learning this for a week).

The things I've been trying have been along the lines of:

SELECT customerName

FROM Deposit

WHERE balance > ALL;

The query should return 1 value, which would be the customerName with the highest balance value.

Thanks a lot for you help :)!

Answer Source

You are looking for the customer(s) whose balance is greater than or equal to all balances in the table

so you just need to use >= instead of >

SELECT customerName
FROM   Deposit
WHERE  balance >= ALL (SELECT balance
                       FROM   Deposit); 

Or you can use a correlated sub query and look for customers whose balances are greater than all other balance values.

SELECT customerName
FROM   Deposit d1
WHERE  balance > ALL (SELECT balance
                      FROM   Deposit d2
                      WHERE  d2.balance <> d1.balance); 

In the event of ties both queries will return all customers with the highest amount.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download