Selecting a Record With MAX Value

Note: An incorrect revision of this answer was edited out. Please review all answers.

A subselect in the WHERE clause to retrieve the greatest BALANCE aggregated over all rows. If multiple ID values share that balance value, all would be returned.

SELECT 
  ID,
  BALANCE
FROM CUSTOMERS
WHERE BALANCE = (SELECT MAX(BALANCE) FROM CUSTOMERS)

What do you mean costs too much? Too much what?

SELECT MAX(Balance) AS MaxBalance, CustomerID FROM CUSTOMERS GROUP BY CustomerID

If your table is properly indexed (Balance) and there has got to be an index on the PK than I am not sure what you mean about costs too much or seems unreliable? There is nothing unreliable about an aggregate that you are using and telling it to do. In this case, MAX() does exactly what you tell it to do - there's nothing magical about it.

Take a look at MAX() and if you want to filter it use the HAVING clause.

Here's an option if you have multiple records for each Customer and are looking for the latest balance for each (say they are dated records):

SELECT ID, BALANCE FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DateModified DESC) as RowNum, ID, BALANCE
    FROM CUSTOMERS
) C
WHERE RowNum = 1