Group By and Inner Join Together To Get Unique Values By Maximum Date-CodePudding

I have a table here in which I want to write a SELECT query in SQL Server that allows me to get the following:

For each unique combination of SalesPerson x Country, get only the rows with the latest Upload_DateTime

However, I am trying to do a group-by and inner join, but to no avail. My code is something like this:

SELECT t1.[SalesPerson], t1.[Country], MAX(t1.[Upload_DateTime]) as [Upload_DateTime]
  FROM [dbo].[CommentTable] AS t1
   GROUP BY t1.[SalesPerson], t1.[Country] 
  INNER JOIN SELECT * FROM [dbo].[CommentTable] as t2 ON t1.[SalesPerson] = t2.[SalesPerson], t1.[Country] = t2.[Country]

It seems like the GROUP BY needs to be done outside of the INNER JOIN? How does that work? I get an error when I run the query and it seems my SQL is not right.

CodePudding user response：

Basically, this subquery will fetch the person, the country and the latest date:

SELECT 
SalesPerson, Country, MAX(uplodaed_datetime)
FROM CommentTable
GROUP BY SalesPerson, Country;

This can be used on a lot of ways (for example with JOIN or with an IN clause).

The main query will add the remaing columns to the result.

Since you tried a JOIN, here the JOIN option:

SELECT
c.id, c.SalesPerson, c.Country,
c.Comment, c.uplodaed_datetime
FROM 
CommentTable AS c
INNER JOIN 
(SELECT 
SalesPerson, Country, 
MAX(uplodaed_datetime) AS uplodaed_datetime
FROM CommentTable
GROUP BY SalesPerson, Country) AS sub
ON c.SalesPerson = sub.SalesPerson
AND c.Country = sub.Country
AND c.uplodaed_datetime = sub.uplodaed_datetime
ORDER BY c.id;

Try out: db<>fiddle