sql - select bigger value's records in a query result

Question

For instance, I do

select * from tblClientInfo
with some condition

and there are 2 records:

 ClientID=001, Salary=500. 
 ClientID=001, Salary=550. 

How Can I just select the record with the highest Salary? FYI, there might be more than 2 records as well.

I know how to asc sort but I don't know how to just select one of them.

This is my code

Select A.AgencyID,19,291,
BETAC.FirstName,BETAC.LastName,BETAC.Phone,BETAC.EMailAddress,BETAC.AnnualIncome,BETAC.Occupants,BETAC.Children2,
BETAC.Children5,ISNULL(BETAC.Children17, 0) ,BETAC.Elderlys,BETAC.Disableds,
0,0,TKFT.FuelTypeID,TKV1.LookupValueID,
BETAC.LandlordName,BETAC.LandlordAddress,BETAC.LandlordCity,BETAC.LandlordState,BETAC.LandlordZip,
BETAC.LandlordPhone,BETAC.HeatInRent,BETAC.DHWInRent,'2018/01/01',BETAC.FirstPrintedDate,
BETAC.StatusDate,NULL,NULL,ISNULL(BETAC.PovertyLevel,0),
BETAC.HighUse,BETAC.HighBurden,ISNULL(BETAC.Section8Housing,0),NULL,NULL,0,BETAC.IsRequestWNZ,
BETAC.SignatureConfirmation,BETAC.MoveInOut,CO.CountyID,
0,0,0,1,BETAC.BenefitAmount,0,1,U.UserID,BETAC.ClientNumber 

from LABeta_FA.dbo.tblClientInfo as BETAC
join LABeta_FA.dbo.tgAgency as BETAA on BETAA.AgencyID=BETAC.AgencyID
join tgAgency as A on BETAA.Phone COLLATE DATABASE_DEFAULT=A.Phone COLLATE DATABASE_DEFAULT
join LABeta_FA.dbo.tgClientStatus as LACS on LACS.ClientStatusID=BETAC.ClientStatusID
--join tlkpValues as TKV on TKV.DisplayName COLLATE DATABASE_DEFAULT=LACS.ClientStatus COLLATE DATABASE_DEFAULT
join LABeta_FA.dbo.tlkpHeatingSource as LATKHS on LATKHS.HeatingID=BETAC.FuelTypeID
join tlkpFuelType as TKFT on TKFT.FuelType COLLATE DATABASE_DEFAULT=LATKHS.HeatingSource COLLATE DATABASE_DEFAULT
join LABeta_FA.dbo.tlkpBuildingType as TKBT on TKBT.BuildingTypeID=BETAC.BuildingTypeID
join tlkpValues as TKV1 on TKV1.DisplayName COLLATE DATABASE_DEFAULT=TKBT.BuildingType COLLATE DATABASE_DEFAULT
join LABeta_FA.dbo.tgUser as LAU on LAU.UserID=BETAC.UserID
join tgUser as U on U.UserName COLLATE DATABASE_DEFAULT=LAU.UserName COLLATE DATABASE_DEFAULT
join LABeta_FA.dbo.tgCounty as LACO on LACO.CountyID=BETAC.CountyID
join tgCounty as CO on CO.County COLLATE DATABASE_DEFAULT=LACO.County COLLATE DATABASE_DEFAULT
where BETAC.LastDate>'01/01/2018' and
BETAC.FirstName='Brandon'

Then the result is like

brandon benefitamount=400 date=2018/01/01
brandon benefitamount=450 date=2019/01/01

I want the result to be the highest benefitamount one.

标签： sqlsql-servertsql

Answer 1

利用max()

 select max(Salary) from tblClientInfo where ClientID=001

如果要选择表的所有列，可以使用子查询

 select * from tblClientInfo 
 where Salary=(select max(Salary) from tblClientInfo where ClientID=001
            ) and ClientID=001