Selecting the most recent entry on a timestamp cell

Question

I have these two tables:

User:
=======================
  id  |  Name | Email 
=======================
  1   | User-A| a@mail
  2   | User-B| b@mail
=======================

Entry:
=================================================
  id  |  agree |       createdOn        | userId
=================================================
  1   | true   | 2020-11-10 19:22:23    |   1
  2   | false  | 2020-11-10 22:22:23    |   1
  3   | true   | 2020-11-11 12:22:23    |   1
  4   | true   | 2020-11-04 22:22:23    |   2
  5   | false  | 2020-11-12 02:22:23    |   2
================================================

I need to get the following result:

=============================================================
  Name    |   Email    |   agree     |       createdOn           
=============================================================
 User-A   |  a@mail    |   true      |  2020-11-11 22:22:23
 User-B   |  b@mail    |   false     |  2020-11-12 02:22:23
=============================================================

The Postgres query I'm running is:

select distinct on (e."createdOn", u.id)
u.id , e.id ,u."Name" , u.email, e.agree, e."createdOn" from "user" u
inner join public.entry e on u."id" = e."userId"
order by "createdOn" desc

But the problem is that it returns all the entries after doing the join! where I only want the most recent entry by the createdOn cell.

Answer 1

You want the latest entry per user. For this, you need the user id in the distinct on clause, and no other column. This guarantees one row in the resultset per user.

Then, you need to put that column first in the order by clause, followed by createdOn desc. This breaks the ties and decides which row will be retained in each group:

select distinct on (u.id) u.id , e.id ,u."Name" , u.email, e.agree, e."createdOn" 
from "user" u
inner join public.entry e on u."id" = e."userId"
order by u.id, "createdOn" desc