Postgresql: get first item of an ordered group not working

Question

I have this data:

| id     | person_id | date                |
|--------|-----------|---------------------|
| 313962 | 1111111   | 2016-04-14 16:00:00 | --> this row
| 313946 | 2222222   | 2015-03-13 15:00:00 | --> this row
| 313937 | 1111111   | 2014-02-12 14:00:00 |
| 313944 | 1111111   | 2013-01-11 13:00:00 | 
| ...... | .......   | ................... | 

-What I would like to select are the indicated rows, i.e. the rows with the most recent date for each person_id.

-Also the output format for the date must be dd-mm-YYYY

So far I was trying with this:

SELECT
    l.person_id,
    to_char(DATE(l.date), 'dd-mm-YYYY') AS user_date
FROM login l
group by l.person_id
order by l.date desc

I was trying different approaches, but I have all kind of Aggregation error messages such as:

for select distinct order by expressions must appear

And

must appear in the GROUP BY clause or be used in an aggregate function

Any idea?

Answer 1

There are several ways, but the simplest way (and perhaps more efficient - but not SQL standard) is to rely on Postgresql's DISTINCT ON:

SELECT DISTINCT ON (person_id ) 
id, person_id , date                
FROM login 
ORDER BY person_id , date desc

The date formatting (do you really want that?) can be done in a outer select:

  SELECT id,person_id, to_char(DATE(date), 'dd-mm-YYYY') as date
  FROM ( 
    SELECT DISTINCT ON (person_id ) 
    id, person_id , date                
    FROM login 
    ORDER BY person_id, date desc )  
  AS XXX;

Answer 2

You need something like the following to know which date to grab for each person.

select l.person_id, to_char(DATE(d.maxdate), 'dd-mm-YYYY')
from login l
inner join
    (select person_id, max(date) as maxdate
    from login group by person_id) d on l.person_id = d.person_id
order by d.maxdate desc

Answer 3

You can do it with a subquery, something like this:

SELECT
    l.person_id,
    to_char(DATE(l.date), 'dd-mm-YYYY') AS user_date
FROM login l
where l.date = (select max(date) from login where person_id =  l.person_id)
order by l.person_id