Selecting a Record With MAX Value

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[tsql] Selecting a Record With MAX Value

Examples related to tsql

Examples related to aggregate-functions