Nicky Cymbals Nicky Cymbals - 2 months ago 8
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?

SELECT CUSTNO, CUSTBAL
FROM CUSTOMER
WHERE CUSTBAL

Answer

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)
;