Selecting a Record With MAX Value

In SQL Server 2008 I have a table CUSTOMERS that has two columns as:

ID, BALANCE

How can I write the query that selects the ID of the customer who has maximum balance, "in the most effective way"?

Option 1: ORDER BY BALANCE and SELECT TOP(1) --> costs too much.

Option 2: Firstly Get MAX amount, then make another query that uses the amount in where clause --> costs too much and not seem reliable.

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)

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

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.

Say, for an user, there is revision for each date. The following will pick up record for the max revision of each date for each employee.

select job, adate, rev, usr, typ 
from tbl
where exists (	select 1 from (	select usr, adate, max(rev) as max_rev 
								from tbl
								group by usr, adate 
							  ) as cond
				where tbl.usr=cond.usr 
				and tbl.adate =cond.adate 
				and tbl.rev =cond.max_rev
			 )
order by adate, job, usr

The query answered by sandip giri was the correct answer, here a similar example getting the maximum id (PresupuestoEtapaActividadHistoricoId), after calculate the maximum value(Base)

select * 
from (
    select PEAA.PresupuestoEtapaActividadId,
        PEAH.PresupuestoEtapaActividadHistoricoId,             
  		sum(PEAA.ValorTotalDesperdicioBase) as Base,
		sum(PEAA.ValorTotalDesperdicioEjecucion) as Ejecucion
    from hgc.PresupuestoActividadAnalisis as PEAA
    inner join hgc.PresupuestoEtapaActividad as PEA
        on PEAA.PresupuestoEtapaActividadId = PEA.PresupuestoEtapaActividadId
    inner join hgc.PresupuestoEtapaActividadHistorico as PEAH
        on PEA.PresupuestoEtapaActividadId = PEAH.PresupuestoEtapaActividadId                                                      	  
    group by PEAH.PresupuestoEtapaActividadHistoricoId, PEAA.PresupuestoEtapaActividadId 	
) as t
where exists (
    select 1 
    from (
        select MAX(PEAH.PresupuestoEtapaActividadHistoricoId) as PresupuestoEtapaActividadHistoricoId             							  							  
	    from hgc.PresupuestoEtapaActividadHistorico as PEAH 					  
		group by PEAH.PresupuestoEtapaActividadId  
    ) as ti
    where t.PresupuestoEtapaActividadHistoricoId = ti.PresupuestoEtapaActividadHistoricoId 
)