Max and Group by functions in SQL Server-CodePudding

my sql got a bit rusty and I was wondering if there is a simple solution for my ask. I'm trying to get two unique UUIDs with with MAX ModifiedTime. Below is my code that I tried to simplify as much as possible. My goal is to get unique UUID with its MAX ModifiedDate along with other two associated columns- desired output 2 rows.

SELECT [UUID],[Description],IsHLVE,ModifiedTime
FROM tblConstructSecureIntelProjectIncidents
WHERE UUID in ('0b666564-38af-4cc2-9a58-58f9b57244b1','4b6080ea-5c13-4f03-8967-a6beb71e80c4')
ORDER BY UUID,ModifiedTime

I tried to group by all columns and do max which doesn't work properly since my "description" and "IsHLVE" have different values.

select [UUID],[Description],IsHLVE,MAX(ModifiedTime)
FROM tblConstructSecureIntelProjectIncidents
WHERE UUID in ('0b666564-38af-4cc2-9a58-58f9b57244b1','4b6080ea-5c13-4f03-8967-a6beb71e80c4')
GROUP BY [UUID],[Description],IsHLVE
ORDER BY UUID

I believe I need some kind of temporary table first and then join it back? My desired output should have two unique UUIDs with its max date including all associated columns.

CodePudding user response：

Sub-query and inner join takes care of this.

SELECT
    t1.uuid
    ,t1.Description
    ,t1.IsHLVE
    ,t1.modifiedtime
FROM tblConstructSecureIntelProjectIncidents as t1
INNER JOIN (
    SELECT
        MAX(modifiedtime) as modifiedtime
        ,uuid
    FROM tblConstructSecureIntelProjectIncidents
    GROUP BY uuid
) as s on t1.uuid = s.uuid and t1.modifiedtime = s.modifiedtime

CodePudding user response：

Perhaps something like this?

SELECT * FROM tblConstructSecureIntelProjectIncidents AS t 
INNER JOIN (
 SELECT [UUID],MAX(ModifiedTime) AS LatestModifiedTime
 FROM tblConstructSecureIntelProjectIncidents
 WHERE UUID in ('0b666564-38af-4cc2-9a58-58f9b57244b1','4b6080ea-5c13-4f03-8967-a6beb71e80c4')
 GROUP BY [UUID]
) AS latest ON latest.UUID = t.UUID AND latest.LatestModifiedTime = t.ModifiedTime
ON latest.UUID

CodePudding user response：

This will get the max modification time per UUID:

select [UUID],MAX(ModifiedTime)
FROM tblConstructSecureIntelProjectIncidents
WHERE UUID in ('0b666564-38af-4cc2-9a58-58f9b57244b1','4b6080ea-5c13-4f03-8967-a6beb71e80c4')
GROUP BY [UUID]

Now you can get the data you want by selecting the values, and JOINing them:

select [UUID],[Description],IsHLVE,MAX(ModifiedTime)
FROM tblConstructSecureIntelProjectIncidents tCSPI
INNER JOIN (
   select [UUID],MAX(ModifiedTime) as MMT
   FROM tblConstructSecureIntelProjectIncidents
   WHERE UUID in ('0b666564-38af-4cc2-9a58-58f9b57244b1','4b6080ea-5c13-4f03-8967-a6beb71e80c4')
GROUP BY [UUID]
) sub ON sub.UUID = tCSPI.UUID AND sub.MMT = tCSPI.ModifiedTime