sql server - Fetching the latest row within a group

Question

I'm currently utilising a series of tables within my TSQL database, which allow me to fetch information about a policyholder (or client). I need to group each policyholder by their DOB and Start of Postcode, as other matches aren't reliable enough.

The issue I am currently having is this will show me the maximum value within that group, rather than the newest value. I sadly cannot provide any data due to GDPR reasons, hopefully someone can guide me in the right direction with my query.

Query:

SELECT MAX([MSM_MarketPricing].[EnquiryKey]) AS EnquiryKey
      ,MAX([TotalNumberOfQuotes]) AS TotalNumberOfQuotes
      ,MAX([MarketPriceTop5Annual]) AS MarketPriceTop5Annual
      ,MAX([DirectInsPriceTop5Annual]) AS DirectInsPriceTop5Annual
      ,MAX([BrokerPriceTop5Annual]) AS BrokerPriceTop5Annual
      ,MAX([BigBrandsPriceTop5Annual]) AS BigBrandsPriceTop5Annual
      ,MAX([MediumBrandsTop5Annual]) AS MediumBrandsTop5Annual
      ,MAX([LittleBrandsPriceTop5Annual]) AS LittleBrandsPriceTop5Annual
      ,MAX([MSM_YourQuotes].[YourAnnualPrice]) AS YourAnnualPrice
      ,'   ' AS ' '

      -- Number Prediction Calculations --
      ,MAX([MarketPriceTop5Annual]) * ((100 +
      (CASE
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
      ELSE -27.48 END)) / 100) AS 'MarketPriceFromBucket'

      -- Number Prediction Calculations --
      ,MAX([DirectInsPriceTop5Annual]) * ((100 +
      (CASE
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
      ELSE -27.48 END)) / 100) AS 'DirectInsPriceFromBucket'

      -- Number Prediction Calculations --
      ,MAX([BrokerPriceTop5Annual]) * ((100 +
      (CASE
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
      ELSE -27.48 END)) / 100) AS 'BrokerPriceFromBucket'

      ,'   ' AS '  '
      ,CAST(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual]) * ((100 +
      (CASE
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
      WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
      ELSE -27.48 END)) / 100) AS INT) as 'CostDiffBucket'
      ,CAST(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual]) AS INT) as 'CostDiff'
      ,FLOOR(ROUND(100.0*(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual])) / MAX([MarketPriceTop5Annual]), 2)) AS PercentDiff
      ,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()), MAX([MSM_PolicyHolder].[DateOfBirth])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()) - 1 END AS INT) AS 'Age'
      ,MAX([MSM_PolicyHolder].[DateOfBirth]) AS DateOfBirth
      ,MAX([MSM_PolicyHolder].[NoClaimsDiscountYears]) AS NoClaimsDiscountYears
      ,MAX([MSM_PolicyHolder].[LicenceHeldForMonths])/12 as 'LicenseLength'
      ,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()), MAX([MSM_Enquiry].[FirstStartedDrivingCar])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()) - 1 END AS INT) AS 'YearsOwned'
      ,MAX([MSM_Enquiry].[CarValue])
      -- Sub-Filters
      ,MAX([MSM_PolicyHolder].[Gender]) AS Gender
      ,MAX([MSM_PolicyHolder].[MaritalStatus]) AS MaritalStatus
      ,MAX([MSM_PolicyHolder].[MainBusinessType]) AS MainBusinessType
      ,CASE WHEN MAX([MIU_BusinessCats].[FTBusinessType]) IS NULL then 'Other' ELSE MAX([MIU_BusinessCats].[FTBusinessType]) END AS 'OccupationIndustry'
      ,MAX([MSM_PolicyHolder].[MainEmploymentStatus]) AS MainEmploymentStatus
      ,MAX([MSM_PolicyHolder].[LicenceType]) AS LicenceType
      ,CASE WHEN MAX(CAST([MSM_PolicyHolder].[NonMotoringConvictions] AS INT)) = 1 THEN 'Yes' ELSE 'No' END AS NonMotoringConvictions
      ,MAX([MSM_PolicyHolder].[AccessToOtherCars]) AS AccessToOtherCars
      ,MAX([MSM_PolicyHolder].[UKResidentSince]) AS UKResidentSince
      ,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()), MAX([MSM_PolicyHolder].[UKResidentSince])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()) - 1 END AS INT) AS 'YearsInTheUK'
      ,CASE WHEN MAX(CAST([MSM_PolicyHolder].[MainDriver] AS INT)) = 1 THEN 'Yes' ELSE 'No' END AS MainDriver
      ,CASE WHEN MAX([MSM_Enquiry].[FuelType]) = 'P' THEN 'Petrol' WHEN MAX([MSM_Enquiry].[FuelType]) = 'D' THEN 'Diesel' ELSE 'Electric' END AS 'FuelType'
      ,MAX([MSM_Enquiry].[BodyShape]) AS BodyShape
      ,MAX([MSM_Enquiry].[AnnualPersonalMileage]) + MAX([MSM_Enquiry].[AnnualBusinessMileage]) AS 'Mileage'
      ,MAX([MSM_Enquiry].[CoverType]) AS CoverType
      ,MAX([MIU_MSMSales].[InsuranceCo]) AS InsuranceCo
      ,MAX([MIU_MSMSales].[AreaGroupBandID]) AS AreaGroupBandID
      ,MAX([MIU_AreaGroupBand].[Description]) AS 'AreaBand'
      ,MAX([MIU_MSMSales].[HostedRatingEngineName])
      ,MAX([MIU_MSMSales].[CreditScore]) AS CreditScore
      ,MAX([MIU_MSMSales].[EngineCC]) AS EngineCC
      ,MAX([MIU_MSMSales].[Garaging]) AS Garaging
      ,MAX([MSM_Enquiry].[EnquiryDateTime]) AS EnquiryDateTime
      ,MAX(CAST([MSM_YourQuotes].[UsedGoToSite] AS INT)) AS 'Click

Answer 1

Sample data

Just inventing some data. Going for a minimal, reproducable example.

create table client
(
  clientid int,
  clientname nvarchar(20),
  clientdob date
);
insert into client (clientid, clientname, clientdob) values
(1, 'Clark Kent',  '1938-04-18'),
(2, 'Bruce Wayne', '1939-03-30');

create table quote
(
  clientid int,
  quotedate date,
  quoteprice money
);
insert into quote (clientid, quotedate, quoteprice) values
(1, '2021-01-01', 1000.0),
(1, '2021-02-01',  995.0),
(2, '2021-01-05', 2000.0),
(2, '2021-01-31', 1900.0);

Issue reproduction

select c.clientid,
       c.clientname,
       c.clientdob,
       max(q.quotedate) as quotedate,  --> gives latest date :)
       max(q.quoteprice) as quoteprice --> does not give latest price :(
from quote q
join client c
  on c.clientId = q.clientid
group by c.clientid,
         c.clientname,
         c.clientdob;

This gives:

clientid  clientname   clientdob   quotedate   quoteprice
--------  -----------  ----------  ----------  ----------
1         Clark Kent   1938-04-18  2021-02-01  1000.0000   --> not latest quote price
2         Bruce Wayne  1939-03-30  2021-01-31  2000.0000   --> not latest quote price

Solution 1

Suggested by charlieface in the question comments.
Number all rows (row_number()) descending on quote date in a common table expression (cte) and take the first row (cte.num = 1) for each client.

with cte as
(
  select c.clientid,
         c.clientname,
         c.clientdob,
         q.quotedate,
         q.quoteprice,
         row_number() over(partition by c.clientid, c.clientname, c.clientdob order by q.quotedate desc) as num
  from quote q
  join client c
    on c.clientId = q.clientid
)
select cte.clientid,
       cte.clientname,
       cte.clientdob,
       cte.quotedate,
       cte.quoteprice
from cte
where cte.num = 1;

Solution 2

Select all individual client info (and construct the client's unique key) and for each client fetch the single last quote information (outer apply).

select c.clientid,
       c.clientname,
       c.clientdob,
       q1.quotedate,
       q1.quoteprice
from client c
outer apply ( select top 1
                     q.quotedate,
                     q.quoteprice
              from quote q
              where q.clientid = c.clientid
              order by q.quotedate desc ) q1;

Result

clientid  clientname   clientdob   quotedate   quoteprice
--------  -----------  ----------  ----------  ----------
1         Clark Kent   1938-04-18  2021-02-01   995.0000  --> latest quote date AND price
2         Bruce Wayne  1939-03-30  2021-01-31  1900.0000  --> latest quote date AND price

Fiddle to see things in action.