SQL the longest date records of an attribute

Question

I have a table called kanban_card_journals which has the following attributes:

• id
• kanban_card_id
• issue_journal_id
• created_at

Where the first three are numbers and the last date and time. This table saves the date of all changes to a kanban card. My problem is I need to know the last time a kanban card was updated, that is, the date of the last update of a kanban_card_id.

I tried the command:

select distinct id, kanban_card_id, created_at from kanban_card_journals where kanban_card_journals.created_at = (select max(created_at) from kanban_card_journals);

It returns me the data from the last updated record, but I don’t know how it does to bring the last of each card.

Answer 1

You are looking for the biggest overall date, without considering each kanban_card_id.

SELECT distinct id, kanban_card_id, created_at FROM kanban_card_journals GROUP BY kanban_card_id HAVING MAX(created_at);

In this query I added the clause GROUP BY and a HAVING at the end. Thus the SELECT will bring the records with the largest dates grouping by kanban_card_id.