How to filter out the records with the latest date?

Question

I have a table with id, task_id, created_taskaction (= rec.cr.date).

I would like to get the latest record for e.g. task_id 0001.

In the wordpress framework I tried this:

> $last_task_action2=$wpdb->get_row("SELECT * FROM $wpdb->task_actions
> NATURAL JOIN ( SELECT id_task, MAX(created_taskaction) AS datetime
> FROM $wpdb->task_actions GROUP BY id_task) mostrecent ON
> $wpdb->task_actions.id_task = $wpdb->mostrecent.id_task  AND
> $wpdb->task_actions.datetime = $wpdb->mostrecent.datetime");

But it doesn't work. I tried copying from this thread, but it's still confusing: https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql

Even these two don't show ANY data...

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM $wpdb->task_actions");
echo $last_task_actiond;

and

$last_task_actiond = $wpdb->get_row("SELECT MAX (created_taskaction) AS 'Max' FROM wp_task_actions");
echo $last_task_actiond;

Nothing shows up...

Answer 1

It worked I found 2 bugs:

Line 5:

$wpdb->task_actions.datetime = $wpdb->mostrecent.datetime"); Had to be: task_actions.created_taskaction instead of task_actions.datetime

Also the $wpdb-> sometimes doesn't work properly, so I changed all of them with wp_