SQL Get records with max value for each group-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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
;

CodePudding user response：

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