CODEWITHSUNDEEP
mysqlsql
user3517914
user3517914

Reputation: 3

Latest date with SQL Query

Thanks in advance for assisting me. I have the following table:

 Name | LastUpdate  |   Amount  |
 ABC  |  2014-4-9   |    100    |
 ABC  |  2014-4-9   |  **101**  |
 ABC  |  2014-4-8   |     99    |
 DEF  |  2014-4-9   |  **200**  |
 DEF  |  2014-4-8   |    160    |
 GHI  |  2014-4-9   |   **50**  |
 GHI  |  2014-4-8   |     80    |

My queries doesn’t seems to work to get the following result: The sum of Amount for the latest date and highest amount for all names.

Example answer for above is 351 (101+200+50).

Upvotes: 0

Views: 78

Answers (1)

Barmar
Barmar

Reputation: 780673

I based this solution on the answer to

find maximum of set of columns for multiple rows in mysql query

SELECT SUM(Amount) AS Total
FROM table1 AS t
WHERE LastUpdate = (SELECT MAX(LastUpdate) 
                    FROM table1 
                    WHERE NAme = t.Name)
AND Amount = (SELECT MAX(Amount) 
              FROM table1
              WHERE Name = t.Name
              AND LastUpdate = t.LastUpdate)

DEMO

Upvotes: 1

Related Questions