How to find lowest value without aggregate function in SQL?

Question

I have this example dataset on Postgres

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
August 2         July 30            Ron        M       S        A         Y          android
August 1         July 30            Ron        M       S        A         Z          browser
July 30          July 30            Ron        M       S        A         Y          android
August 1         July 28            Ana        F       S        B         Y          android
August 1         July 28            Ana        F       S        B         Z          browser
July 28          July 28            Ana        F       S        B         Y          android

I want to find when is the first time the user shown (updated_at) after its activated_at and belongs to which app.

Expected result:

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
July 30          July 30            Ron        M       S        A         Y          android
July 28          July 28            Ana        F       S        B         Y          android

I have tried this SQL:

SELECT min(ut.updated_at), u.activated_at, u.full_name, u.gender, r.name, s.name, ut.app_name, ut.device_type
FROM "public"."user_tokens" ut JOIN
     "public"."users" u
     ON ut.user_id = u.id JOIN
     "public"."user_roles" ur
     ON ut.user_id = ur.user_id JOIN
     "public"."roles" r
     ON ur.role_id = r.id JOIN
     "public"."schools" s
      ON ur.school_id = s.id
WHERE (NOT (ut.app_name) like 'G')
Group by u.activated_at, u.full_name, u.gender, r.name, s.name, ut.app_name, ut.device_type
Order by u.activated_at desc

But the result goes like this:

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
August 1         July 30            Ron        M       S        A         Z          browser
July 30          July 30            Ron        M       S        A         Y          android
August 1         July 28            Ana        F       S        B         Z          browser
July 28          July 28            Ana        F       S        B         Y          android

I have tried to exclude the app_name and device_type from group by clause but it says ERROR: column "ut.app_name" must appear in the GROUP BY clause or be used in an aggregate function

Any idea how to solve it? Any input would be appreciate very much. Thank you.

Answer 1

I think DISTINCT ON is probably the best way to do this on Postgres:

SELECT DISTINCT ON (u.full_name)
    ut.updated_at,
    u.activated_at,
    u.full_name,
    u.gender,
    r.name,
    s.name,
    ut.app_name,
    ut.device_type
FROM "public"."user_tokens" ut
INNER JOIN "public"."users" u ON ut.user_id = u.id
INNER JOIN "public"."user_roles" ur ON ut.user_id = ur.user_id
INNER JOIN "public"."roles" r ON ur.role_id = r.id
INNER JOIN "public"."schools" s ON ur.school_id = s.id
WHERE NOT ut.app_name LIKE 'G'
ORDER BY
    u.full_name,
    ut.updated_at;

The above will return one record for each full name user, corresponding to the earlier updated_at time.