Latheesan Latheesan - 5 months ago 8
SQL Question

Grouping and Ordering result to pick the most recent record in MS SQL

I have the following query written in such a way it returns the data I need:

SELECT SLCA.CustomerAccountNumber, SLCD.DefaultEmail, SOR.DocumentDate, SLST.*
FROM SLCustomerAccount AS SLCA
INNER JOIN SOPOrderReturn AS SOR ON SOR.CustomerID = SLCA.SLCustomerAccountID
INNER JOIN SLCustomerContactDefaultsVw AS SLCD ON SLCD.SLCustomerAccountID = SLCA.SLCustomerAccountID
LEFT OUTER JOIN MMS182SLStatus AS SLST ON SLST.Customer = SLCA.SLCustomerAccountID
WHERE SLCD.IsDefaultRole = 1 AND SLCD.DefaultEmail IS NOT NULL AND SLCD.DefaultEmail <> ''
AND SLCD.DefaultEmail = '...major@live.co.uk'
AND SLST.[Status] IS NULL
ORDER BY SOR.DocumentDate DESC


The only issue is, this email I am using in the where filter yields multiple results like this (albeit being correct):

enter image description here

How can I use
group by
to remove duplicate rows, so I only have get one record for the most recent DocumentDate?

I know i can do
TOP 1
but this is not what I need. Because, I am testing the query now by scoping to one particular email that has duplicate. Later, I need to run this query on all records without this line
AND SLCD.DefaultEmail = '...major@live.co.uk'
- so you can see why I can't use
TOP 1
.

When I tried to use
group by
like this:

SELECT MAX(SLCA.CustomerAccountNumber), MAX(SLCD.DefaultEmail), MAX(SOR.DocumentDate), MAX(SLST.MMS182SLStatus), MAX(SLST.Customer), MAX(SLST.[Status])
FROM SLCustomerAccount AS SLCA
INNER JOIN SOPOrderReturn AS SOR ON SOR.CustomerID = SLCA.SLCustomerAccountID
INNER JOIN SLCustomerContactDefaultsVw AS SLCD ON SLCD.SLCustomerAccountID = SLCA.SLCustomerAccountID
LEFT OUTER JOIN MMS182SLStatus AS SLST ON SLST.Customer = SLCA.SLCustomerAccountID
WHERE SLCD.IsDefaultRole = 1 AND SLCD.DefaultEmail IS NOT NULL AND SLCD.DefaultEmail <> ''
AND SLCD.DefaultEmail = '...major@live.co.uk'
AND SLST.[Status] IS NULL
GROUP BY SLCD.DefaultEmail
ORDER BY MAX(SOR.DocumentDate) DESC


It returns the wrong result:

enter image description here

I was expecting it to return this record:

enter image description here

Any ideas what I might be doing wrong here?

Answer

Use row_number():

with q as (<your query here without the `order by`>)
select q.*
from (select q.*,
             row_number() over (partition by DefaultEmail order by documentdate desc) as seqnum
      from q
     ) q
where seqnum = 1;