Delete repeats and return last Postgresql record

Question

Good afternoon,

I need to make a query to return the description of the records of a table, and there can be no repetitions of ID (of another table) and need to be the most recent record. Consultation:

Records returned (some of them):

Do you see the repeated "obraid" circled in red? Well, I need to return only a record of a certain "obraid" and this needs to be the one that has the most recent date, that is, these that are circulated in green. I’ve tried a lot and I couldn’t fix it. Would anyone have any idea?

Answer 1

You can split your query into two and with a part ,(obraid and data_motion) make a group by by obraid and making the date max, functioning as a table in the FROM clause:

SELECT s.descricao, t.obraid, t.data_movimentacao
FROM (
SELECT o.id obraid, MAX(m.data_movimentacao) data_movimentacao
FROM obras.obra o
JOIN obras.movimento_obra m on o.id = m.obra_id
WHERE o.cliente_id = 106
GROUP BY o.id obraid ) t
JOIN obras.movimento_obra m on t.obraid = m.obra_id and t.data_movimentacao = m.data_movimentacao
JOIN obras.status_obra s on s.id = m.status_id
ORDER BY t.obraid

That query only work if the collumn data_move is a datetime type, but if it’s a CHAR type, you can make the query like this:

SELECT s.descricao, t.obraid, t.data_movimentacao
FROM (
SELECT o.id obraid, to_char(MAX(to_date(m.data_movimentacao,'DD/MM/YYYY')) ,'DD/MM/YYYY') data_movimentacao
FROM obras.obra o
JOIN obras.movimento_obra m on o.id = m.obra_id
WHERE o.cliente_id = 106
GROUP BY o.id obraid ) t
JOIN obras.movimento_obra m on t.obraid = m.obra_id and t.data_movimentacao = m.data_movimentacao
JOIN obras.status_obra s on s.id = m.status_id
ORDER BY t.obraid