I want the MAX(Date) for each distinct ID. This is easy:
SELECT
Id,
MAX(LastModifiedDate)
FROM
[MyServer].[Database].[Table]
GROUP BY
Id;
but I also need to include other columns like first name, last name, company, etc.
which cannot be aggregated, and it won't work if I put them in the GROUP BY clause, because then it returns rows I don't want.
Use that query to filter with the same table by ID and that last modified date.
SELECT
T.*
FROM
[MyServer].[Database].[Table] AS T
INNER JOIN (
select Id,
MAX(LastModifiedDate) AS MaxLastModifiedDate
FROM [MyServer].[Database].[Table]
group by Id
) AS N ON
T.Id = N.Id AND
N.MaxLastModifiedDate = T.LastModifiedDate
EDIT: If you have multiple max dates for each ID, then you can't use their value to filter. You can use a ROW_NUMBER to rank them instead:
;WITH MaxByRowNumber AS
(
SELECT
T.*,
LastModifiedDateRanking = ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.LastModifiedDate DESC)
FROM
[MyServer].[Database].[Table] AS T
)
SELECT
M.*
FROM
MaxByRowNumber AS M
WHERE
M.LastModifiedDateRanking = 1
You might want to add another column to the ORDER BY to untie the most recent updated dates, or not, depending on your needs.
MaxLastModifiedDate. I've edited the answer to use another way around.In order to do this, instead of using group by, try over():
select Id, <any other thing you want>,max(LastModifiedDate) over (partition by Id) as LastModifiedDate
FROM [MyServer].[Database].[Table];
GROUP BY ? This will prevent the use of non-group fields in the SELECT clause
One way would be using a CTE:
with cte as
(
select Id,
max(LastModifiedDate) LastModifiedDate
FROM [MyServer].[Database].[Table]
group by Id
)
select t.*, c.LastModifiedDate
from [MyServer].[Database].[Table] t join cte c on t1.id = cte.id
Figured this out literally as soon as I posted it. I was just confused on the proper way to use a window function.
SELECT [Id]
,LastModifiedDate
,Row_Number() over (Partition by Id order by LastModifiedDate DESC) as rn
,[FirstName]
,[LastName]
,[Company]
,[Title]
FROM [MyServer].[Database].[Table] as C
where rn = 1
What i was trying to do was:
,Row_Number() over (Partition by Id order by MAX(LastModifiedDate) DESC) as rn
but MAX makes no sense because it is DESC
MAX(LastModifiedDate) OVER (PARTITION by ID).GROUP BY or MAX(LastModificationDate) OVER (PARTITION BY ID) is how. If you get duplicate rows, explain that in the question as well. That's why you should provide examples of the input data and desired output tooYou can use subquery :
select t.*
from table t
where LastModifiedDate = (select max(t1.LastModifiedDate)
from table t1
where t1.id = t.id
);
OVER()clause in the aggregate functions instead of aGROUP BY