returning only the latest record from a resultset

Hi all,
I know there is some way to do this that I am just not getting and I hope
someone here can help me. I have written the following query to return a
group of records for a mailing list:
SELECT DISTINCT Company
,NCCIRiskID
,PolicyNumber
,Address1
,City
,State
,Zip
,NumberOfStates
,ClassCode
,EffectiveDate
,ExpirationDate
,CarrierGroupNumber
,CarrierNumber
,FederalIDNumber
,CoverageState
,MODStatus
,LEFT(ExMODFactor,4)AS ExMODFactor
,MODEffectiveRatingDate
,BusinessType
,TotalEstimatedPayroll
,CurrentPremium
,TotalOfAllStatesPremium
,PolicyTotalEstimatedPremium
FROM DATA3.Marketing.dbo.CumulativeMailingList CML
My problem is, I am returning the same Company multiple times if they have
different MODEffectiveRatingDates (or anything else for that matter). I
want to return a record for each individual Company, City, State, Zip
combination that contains the most recent MODEffectiveRatingDate. Can
someone suggest a way to do this?
Any help would be much appreciated,
-ChrisSomething like:
SELECT ...
FROM YourTable AS T
WHERE dt =
(SELECT MAX(dt)
FROM YourTable
WHERE company = T.company)
Assuming that (company,dt) is unique. If not, define how you want to
identify the "latest" row.
David Portas
SQL Server MVP
--|||Chris,
Which columns can we use to identify a group?
what means the most recent MODEffectiveRatingDate?, the maximum
MODEffectiveRatingDate for the group?
AMB
"Chris Ennis" wrote:

> Hi all,
> I know there is some way to do this that I am just not getting and I hope
> someone here can help me. I have written the following query to return a
> group of records for a mailing list:
> SELECT DISTINCT Company
> ,NCCIRiskID
> ,PolicyNumber
> ,Address1
> ,City
> ,State
> ,Zip
> ,NumberOfStates
> ,ClassCode
> ,EffectiveDate
> ,ExpirationDate
> ,CarrierGroupNumber
> ,CarrierNumber
> ,FederalIDNumber
> ,CoverageState
> ,MODStatus
> ,LEFT(ExMODFactor,4)AS ExMODFactor
> ,MODEffectiveRatingDate
> ,BusinessType
> ,TotalEstimatedPayroll
> ,CurrentPremium
> ,TotalOfAllStatesPremium
> ,PolicyTotalEstimatedPremium
> FROM DATA3.Marketing.dbo.CumulativeMailingList CML
> My problem is, I am returning the same Company multiple times if they have
> different MODEffectiveRatingDates (or anything else for that matter). I
> want to return a record for each individual Company, City, State, Zip
> combination that contains the most recent MODEffectiveRatingDate. Can
> someone suggest a way to do this?
> Any help would be much appreciated,
> -Chris
>
>|||yup, that did it David. Thanks a ton for helping me through that
braincramp.
-Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112626008.170718.292670@.o13g2000cwo.googlegroups.com...
> Something like:
> SELECT ...
> FROM YourTable AS T
> WHERE dt =
> (SELECT MAX(dt)
> FROM YourTable
> WHERE company = T.company)
> Assuming that (company,dt) is unique. If not, define how you want to
> identify the "latest" row.
> --
> David Portas
> SQL Server MVP
> --
>