I have 2 tables in postgres.

users

auth0_logs

I am trying to get the last login information (a single row in the auth0_logs table based on MAX(auth0_logs.date) ) for for each unique user (auth0_logs.user_id) joined to the users table on user.auth0_id.

[
    {
        // auth0_logs information
        user_id: "123-A",
        last_login: "021-10-16T00:18:41.381Z",
        client_name: "example_client",

        // users information
        email: "a@a"
    },
    {
        user_id: "123-B",
        last_login: "...",
        client_name: "example_client",
        email: "b@b"
    },
    {
        user_id: "123-C",
        last_login: "...",
        client_name: "example_client",
        email: "c@c"
    }
]

I know this is a problem with "bare" columns not being allowed in queries that use aggregators (without being added to the GROUP BY -- but adding to the GROUP BY returned > 1 row) but I cannot get a solution that works from other SO posts (best post I've found: SQL select only rows with max value on a column). I promise you I have been on this for many hours over the past few days ....

-- EDIT: start --

I have removed my incorrect attempts as to not confuse / misdirect future readers. Please see @MichaelRobellard answer using the WITH clause based on the above information.

-- EDIT: end --

Any help or further research direction would be greatly appreciated!

CodePudding user response：

with user_data as (
    select user_id, max(date) from auth0_logs group by user_id
)
select * from user_data
join auth0_logs on user_data.user_id = auth0_logs.user_id 
    and user_data.date = auth0_logs.date
join users on user_data.user_id = users.auth0_id

CodePudding user response：

with
t as 
(
 select distinct on (user_id) * 
 from login_logs 
 order by user_id, ldate desc
),
tt as 
(
 select auth0_id user_id, ldate last_login, client_name, email
 from t join users on auth0_id = user_id
) 
select json_agg(to_json(tt.*)) from tt;

SQL fiddle here.