I have a series of records that are related by a reference.
What I am looking to do is show me only the record with the most recent date and time, example:
I have the following logs:
The record with the most recent date and time would be that of id 405 says that the end date was 07/28/2020 and the end time was at 1:47 in the afternoon.
I have the following query:
$desbloq="select * from p_atendidas where refe='$pro' ORDER BY fechafin,horafinal DESC LIMIT 1";
$okdesblo=mysqli_query($con,$desbloq);
$posdesbl=mysqli_fetch_assoc($okdesblo);
But it's wrong because it brings me another record that is not.
How could I fix the query so that it brings the value with the most recent date and time.
Via 2 subqueries:
Being that way:
Both queries will return a single row for each case, the maximum value in each scenario and with that we can compose the filter indicating with the operator
AND
to make the selection where both values are the maximum registered.I think your best option is to concatenate the
fechafin
and thehorafinal
to get the value of the maximum date and time inserted in the DB, since it occurs to me that if you do it independently you could run into an error where there is afechafin
maximum date but thehorafinal
maximum does not correspond to the date, that said, you could try this, after concatenating the date and time, use the functionSTR_TO_DATE
to convert the text fields to valid dates and times for MySQL and thus perform the comparison: