1

I have this query :

SELECT  Truck.Name AS name,
        timestamp,
        oil,
        diesel,
        cargo,
        Truck.notes AS Remarks

        FROM trip
        INNER JOIN Truck USING (idTruck)
        WHERE idTruck IN('2','4','5','6','7','8','9','11','12','13','14','15','16')
        ORDER BY name, timestamp DESC

which returns ALL records from the ID's I was thinking of using CURDATE as 

AND DATE(timestamp) = CURDATE()

in order to get last records but when a record is not modified a day (they usually do but sometimes this is not the case) I'm loosing the records . How would I modify the query to get the last entry of each idTruck regardless of the timestamp in a single query ?

Rick James
  • 135,179
  • 13
  • 127
  • 222
George Papas
  • 15
  • 4

1 Answers1

0

I suggest you use the MAX function of sql to get the last record (MAX(Truck.Name)):

SELECT  MAX(Truck.Name) AS name,
        timestamp,
        oil,
        diesel,
        cargo,
        Truck.notes AS Remarks

FROM trip
INNER JOIN Truck USING (idTruck)
WHERE idTruck IN('2','4','5','6','7','8','9','11','12','13','14','15','16')
ORDER BY name, timestamp DESC

To get the last record of each ID :

SELECT  MAX(Truck.Name) AS name,
        timestamp,
        oil,
        diesel,
        cargo,
        Truck.notes AS Remarks

FROM trip
INNER JOIN Truck USING (idTruck)
WHERE idTruck IN('2','4','5','6','7','8','9','11','12','13','14','15','16')
GROUP BY idTruck 
ORDER BY name, timestamp DESC

because according to sql.sh(https://sql.sh/fonctions/agregation/max):

the aggregation function MAX() allows to return the maximum value of a column in a record set. The function can be applied to numeric or alphanumeric data. For example, it is possible to search for the most expensive product in a table in an online shop.

Dadinaks
  • 93
  • 8
  • This brings back only one idTruck and not the last of each idTruck in the table. I want to get the last entry of all the id's in my where clause in a single query if possible. – George Papas Jun 11 '20 at 13:05
  • then you only have to add a **GROUP BY idTruck** like this : `SELECT idTruck, MAX(Truck.Name) AS name,timestamp, oil, diesel, cargo, Truck.notes AS Remarks FROM trip INNER JOIN Truck USING (idTruck) WHERE idTruck IN('2','4','5','6','7','8','9','11','12','13','14','15','16') GROUP BY idTruck ORDER BY name, timestamp DESC ` . Wait. I'll change my answer if that's okay. – Dadinaks Jun 11 '20 at 13:26
  • 1
    I'm accepting your answer with a little change of mine : I put MAX(timestamp) and voila : – George Papas Jun 11 '20 at 13:31
  • Downvoting because it **DOES NOT WORK**. `GROUP BY` plus `MAX` will correctly get the 'last' value for each group, but it _does not_ get the rest of the columns that correspond to that max. You must then `JOIN` back to the table to get the other columns that correspond to the max. See the `[groupwise-maximum]` tag that I added. – Rick James Jun 12 '20 at 17:46
  • 1
    @Dadinaks - Sorry to be harsh on a Newbie. Your comments about `MAX()` are good. But the overall answer is a common mistake; I could not let it stand as the accepted and upvoted answer. – Rick James Jun 12 '20 at 17:51
  • I don't know what you mean doesn't work , It worked perfect for what I wanted with the change I made (replaced MAX(Truck.Name with MAX(timestamp) , as I described in the comment. But who knows, perhaps you now better. – George Papas Jun 15 '20 at 07:08
  • As much as it hurts you're right . When adding more records it doesn't work as expected, it returns wrong data. Back to start.... – George Papas Jun 16 '20 at 08:52
  • ah okay, no problem. I actually read an article on [groupwise-maximum] at this link: [Groupwise Max in MySQL](http://mysql.rjweb.org/doc.php/groupwise_max) and I'm gonna try it first because it's new to me. I'll change my answer if I find a new request. I'm sorry for the mistakes I've made. Thanks for the remark @Rick James. – Dadinaks Jun 18 '20 at 08:14