Skip to content
Advertisement

Access Bare Columns w/ Aggregate Function w/o adding to Group By

I have 2 tables in postgres.

users

auth0_id email
123-A a@a
123-B b@b
123-C c@c

auth0_logs

id date user_id client_name
abc-1 021-10-16T00:18:41.381Z 123-A example_client
abc-2 123-A example_client
abc-3 123-B example_client
abc-4 123-A example_client
abc-5 123-B example_client
abc-6 123-C example_client

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!

Advertisement

Answer

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
Advertisement