Geek asks and answers

SQL joins 3 tables and gets only the row with the last date

I have 3 tables user, sessionand log. The user table stores all information related to the user, while the session simply connects the user to the log. And I want to get a list of all users with the latest registration. The design of the table is as follows:

user (id, name, ...)
session (id, user_id)
log (id, session_id, time, type, ...)

My current request looks like this:

SELECT * 
FROM   USER AS u 
       INNER JOIN session AS s 
               ON u.id = s.user_id 
       INNER JOIN log AS l 
               ON l.session_id = s.id 
ORDER  BY l.time DESC

But it is easy to imagine that this simply returns the data of all three tables, sorted by date. How to achieve the result that I get each user only once with the data from the last log entry, sorted by log time (desc)?

Thanks in advance for your help.

+3
source share
2 answers

DISTINCT ON ORDER BY, . , :

SELECT DISTINCT ON (u.id) 
    u.id,
    u.Name,
    l.type,
    l.time
FROM user AS u 
    INNER JOIN session AS s ON u.id = s.user_id
    INNER JOIN log AS l ON l.session_id = s.id
ORDER BY u.id, l.time DESC;

N.B. , , , , SELECT *

, - max user_id, :

SELECT  u.id,
        u.Name,
        l.type,
        l.time
FROM    user AS u 
        INNER JOIN session AS s 
            ON u.id = s.user_id
        INNER JOIN log AS l 
            ON l.session_id = s.id
        INNER JOIN
        (   SELECT  s.user_id, MAX(l.time) AS time
            FROM    session AS s
                    INNER JOIN log AS l
                        ON l.session_id = s.id
            GROUP BY s.user_id
        ) AS MaxLog
            ON MaxLog.user_id = u.id
            AND MaxLog.time = l.time
ORDER BY l.time DESC;

ROW_NUMBER():

SELECT  id, Name, type, time
FROM    (   SELECT  u.id,
                    u.Name,
                    l.type,
                    l.time,
                    ROW_NUMBER() OVER(PARTITION BY u.id ORDER BY l.time DESC) AS RowNumber
            FROM    user AS u 
                    INNER JOIN session AS s 
                        ON u.id = s.user_id
                    INNER JOIN log AS l 
                        ON l.session_id = s.id
        ) u
WHERE   RowNumber = 1;
+2

(user.user_name?), Max:

SELECT u.user_id, 
        u.user_name, 
       Max(l.time) AS LastLogTime 
FROM   USER AS u 
       LEFT JOIN session AS s 
              ON u.id = s.user_id 
       INNER JOIN log AS l 
               ON l.session_id = s.id 
GROUP  BY u.user_id, 
          u.user_name;

select *, GROUP BY , ORDER BY l.time - , . user_name LEFT JOINED - , , , , LastLogTime NULL.

+1

More articles:


All Articles