[tsql] 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.

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.

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 
)

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