SQL Get records with max value for each group

user_11168878 0
Viewed 45

I have 2 tables Journal and Users Journal looks like this:

TransTime RegNumber UserID
5/26/2022 11:00:00 101 3
5/26/2022 11:30:00 102 2
5/26/2022 13:00:00 101 5
5/26/2022 14:30:00 103 4
5/26/2022 15:00:00 102 1

Users table

UserID Name
1 Ross
2 Rachel
3 Chandler
4 Monica
5 Joey

What I would like to do is get a table of the Registers and their most recent user names. This should seem very simple. But since I am joining tables on the userID, I am getting all 5 records on the first table. But it should look like this:

RegNumber LastUser
101 Joey
102 Ross
103 Monica

I have tried a variety of solutions but haven't found the right one. Any help is appreciated.

sql
5 Answers

if you start with an inner join of the max transtime per regNumber, then join with user table:

Select J.RegNumber, U.Name
From Journal J
Inner join 
(Select Max(TransTime) as TransTime, RegNumber 
From Journal
Group by RegNumber) J2 on J.TransTime = J2.TransTime and J.RegNumber = J2.RegNumber
Inner join
Users U on J.UserID = U.UserID
user_16162600

Here is an option using a CTE:

;with cte as
(
    Select  RegNumber,
            UserID = max(UserID)
    From journal
    group by RegNumber
)
Select  RegNumber = C.RegNumber,
        LastUser = U.Name
From cte C
Join users U ON U.Userid = C.UserID
order by C.RegNumber
user_57147910

This answer is not, at its core, substantively different from the others. However, in terms of being helpful to the target audience it's more readable, more self-documenting, and more standard in terms of formatting.

Sidebar: This SQL takes the data design at face value, as a given, with the implicit assumption that TransTime is the PK or at least uniquely indexed, possibly in conjunction with RegNumber. Bottom line, it would be good to have a little more info about the key structure along with the original question.

WITH LatestEntries AS
(
    SELECT
        MAX(TransTime) AS LatestTimeForReg
        ,RegNumber
    FROM
        Journal
    GROUP BY
        RegNumber
)
SELECT
    J.RegNumber
    ,U.[Name] AS LastUser
FROM
    LatestEntries LE
    INNER JOIN Journal J ON LE.LatestTimeForReg = J.TransTime AND LE.RegNumber = J.RegNumber
    INNER JOIN Users U ON J.UserID = U.UserID
ORDER BY
    J.RegNumber
;
user_66639220

You can use a temptable or cte structure to rank your data based on RegNo and Trantime like below, then retrieve the most updated users for each journal:

CREATE TABLE #Journals (TranTime DATETIME, RegNo INT, UserId INT)
CREATE TABLE #Users (UserId INT, UserName NVARCHAR(100))

INSERT INTO #Users VALUES(1,'Ross'),(2,'Rachel'),(3,'Chandler'),(4,'Monica'),(5,'Joey')

INSERT INTO #Journals VALUES ('5/26/2022 11:00:00',101,3),('5/26/2022 11:30:00',102,2),
('5/26/2022 13:00:00',101,5),('5/26/2022 14:00:00',103,4),('5/26/2022 15:00:00',102,1)
    
;WITH cte as (
        SELECT *,rn=ROW_NUMBER() OVER (PARTITION BY RegNo ORDER BY TranTime DESC)
        FROM #Journals
    )
SELECT RegNo, u.UserName
FROM cte
INNER JOIN #Users u ON u.UserId = cte.UserId
WHERE rn=1 --since sort by TranTime is descending, it'll give you the latest user for each specific RegNo
ORDER BY RegNo

Tested and it works on SQL Server 2016.

user_17613070 
select u.Name, j.* 
from journal j 
inner join ( 
    select max(TransTime) last_update, RegNumber 
    from journal 
    group by RegNumber 
) t1 
inner join j.RegNumber = t1.RegNumber 
   and t1.last_update = j.TransTime 
left join Users_Journal uj on j.UserID= uj.UserID
user_84127050
Related