Get several data from SQL query based on different conditions

I have the following code:

SELECT DISTINCT m.solde_total_client
               ,c.client_nom
               ,co.contenant_nom
FROM `mouvement` m, `client` c, `contenant` co
WHERE c.client_id = m.client_id
AND co.contenant_id = m.contenant_id
ORDER BY m.movement_date DESC
LIMIT 1;

And I get as a result one total sold of one client. But I want to get one for each contenant for each client. (But it still need to be the last one by date)

I’m getting as a result: And I want to get several result like that such as:

Leclerc | Geobox | 50
SuperU  | Box    | 40
...

sold_total_client is what the client as after a shipment, there is several shipment and the sold is updated at every move, so the last one by date is the actual sold. So I have to get the last move of every contenant of every client.

Answer

You could try using a subquery for max_date group by client_id, contenant_id

SELECT 
    m.solde_total_client,
    m.`mouvement_date`,
    c.client_nom,
    co.contenant_nom  
FROM  
    `mouvement` m
INNER JOIN  
    (SELECT 
         MAX(mouvement_date) max_date, client_id, contenant_id
     FROM
         `mouvement`
     GROUP BY
         client_id, contenant_id) t ON t.client_id = m.client_id 
                                    AND m.contenant_id = t.contenant_id
                                    AND t.max_date = m.`mouvement_date`
INNER JOIN  
    `client` c ON c.client_id = m.client_id 
INNER JOIN  
    `contenant` co ON  co.contenant_id = m.contenant_id
ORDER BY 
    m.`mouvement_date`