Nicky Cymbals Nicky Cymbals - 1 year ago 73
SQL Question

Selecting highest value without using max?

I have 2 columns. CustNo and CustBal

Trying to write script to select CustNo with the highest balance. I tried using max and I can get that to work if I just use CustBal column but not in this case. I think it could be a WHERE item with something after CUSTBAL?


Answer Source

Why without using MAX?

You need to compute the MAX separately, because you can only do that at the end of a query (using the aggregate function MAX in the SELECT clause), and you can't mix individual rows and aggregates in one query. So you must compute the MAX in a subquery.

select custno, custbal
from   customer
where  custbal = (select max(custbal) from customer)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download