Multiple Results in Max Query

Transact-SQL
1

I am getting multiple results in my Max query such as this:
Company PolicyExpDate
CompanyA 1/17/20
CompanyA 1/17/19

I only need the line with the Max date to appear in the report. Here is my query as of now:

SELECT
  tblContractor.CompanyName
  ,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
  ,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
  ,Max(tblInsurance.PolicyExpDate) AS MaxPolicyExpDate
FROM
  tblContractor
  INNER JOIN  tblInsurance
    ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
GROUP BY tblContractor.CompanyName, tblContractor.BMSIContractorID, tblInsurance.BMSIContractorID, tblInsurance.PolicyExpDate

I've tried a couple things such as an ON statement after Group By but I'm not thinking clearly on this. Thanks for any help!

2 
;with src
as
(
SELECT tblContractor.CompanyName
      ,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
      ,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
	  ,ROW_NUMBER() OVER (
		  PARTITION BY CompanyName
		  ORDER BY tblInsurance.PolicyExpDate desc
	   ) row_num
 FROM tblContractor
INNER JOIN  tblInsurance
   ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
)
select *
  from src
  where row_num = 1
1 Like
3 
SELECT
  tblContractor.CompanyName
  ,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
  ,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
  ,tblInsurance.PolicyExpDate AS MaxPolicyExpDate
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY BMSIContractorID ORDER BY PolicyExpDate DESC) AS row_num
    FROM tblInsurance
) AS tblInsurance
INNER JOIN 
  tblContractor 
  ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID AND tblInsurance.row_num = 1
WHERE tblInsurance.row_num = 1
2 Likes
4

Scott, your solution is what I needed, thank you! And thank you Yosiasz as well.

Scott, if I wanted to add two more date columns to the query where I would also be seeking those Max dates what would that query look like?
Such as:
Company PolicyExpDateA PolicyExpDateB PolicyExpDateC

5

You're welcome!

Give this a shot:

SELECT
  tblContractor.CompanyName
  ,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
  ,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
  ,tblInsurance.PolicyExpDateA
  ,tblInsurance.PolicyExpDateB
  ,tblInsurance.PolicyExpDateC
FROM (
    SELECT
        BMSIContractorID
        ,MAX(CASE WHEN row_num = 1 THEN PolicyExpDate END) AS PolicyExpDateA
        ,MAX(CASE WHEN row_num = 2 THEN PolicyExpDate END) AS PolicyExpDateB
        ,MAX(CASE WHEN row_num = 3 THEN PolicyExpDate END) AS PolicyExpDateC
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY BMSIContractorID ORDER BY PolicyExpDate DESC) AS row_num
        FROM tblInsurance
    ) AS query1
    WHERE row_num BETWEEN 1 AND 3
    GROUP BY BMSIContractorID
) AS tblInsurance
INNER JOIN 
  tblContractor 
  ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
--ORDER BY CompanyName
--ORDER BY tblInsurance.BMSIContractorID
1 Like
6

Thank you!!