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.
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
The main query will add the remaing columns to the result.
Since you tried a
JOIN, here the
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