CODEWITHSUNDEEP
mysqlsqldatabase
user896326
user896326

Reputation:

SQL - Group By where rows have the most recent date

I am having the following tables and I was wondering how to do a query on it.

Table Recipe

ID | Name
0  | Apple pie
1  | Pizza

Table Ingredient

RecipeID  | Timestamp | Name    | Price
0         | 10-2-2014 | Apple   | 1.20
0         | 7-2-2014  | Apple   | 1.14
0         | 9-2-2014  | Flour   | 2.00
1         | 9-2-2014  | Tomato  | 1.12

How can I do a Group By RecipeID on this so I get the following results

RecipeID  | Name      | Total_Latest_Ingredient_Price
0         | Apple Pie | 3.20
1         | Pizza     | 1.12

The idea is that the Query should only take the ingredients with the latest dates. So that the price for an apple from 10-2-2014 will be taken and not the one (or both) from 7-2-2014. So only the latest dates.

This might be a tough cookie ( or not! ) but I hope you can help me out!

Upvotes: 0

Views: 303

Answers (4)

Fabricator
Fabricator

Reputation: 12782

Yet another implementation:

select a.recipeid, c.name, sum(a.price) total_latest_ingredient_price
from ingredient a
join (
  select recipeid, name, max(timestamp) m
  from ingredient 
  group by recipeid, name) b on a.recipeid = b.recipeid and a.name = b.name and a.timestamp = b.m
join recipe c on a.recipeid = c.id
group by a.recipeid;

fiddle

Upvotes: 0

spencer7593
spencer7593

Reputation: 108500

Assuming the combination of (RecipeID, Timestamp, Name) is unique on the Ingredient table, and assuming that column named Timestamp is stored in a canonical form (e.g. either MySQL DATE, DATETIME, TIMESTAMP datatype or a character datatype with format such that a comparison of values will always yield a "latest" Timestamp value...

The normative pattern is to use an inline view to retrieve the "latest" Timestamp, and then use a JOIN operation to retrieve the entire row.

SELECT g.RecipeID
     , SUM(g.Price) AS `Total_Latest_Ingredient_Price`
  FROM Ingredient g
  JOIN (SELECT h.RecipeID
             , h.Name
             , MAX(h.Timestamp) AS `Timestamp`
          FROM Ingredient h
         GROUP
            BY h.RecipeID
             , h.Name
       ) i
    ON i.RecipeID  = g.RecipeID
   AND i.Name      = g.Name
   AND i.Timestamp = g.Timestamp
 GROUP BY g.RecipeID

The inline view aliased as i gets the "latest" Timestamp (assuming, again, that Timestamp column is canonical form, such that the "maximum" value is guaranteed to be the "latest" value. This will be true if datatype of the column named Timestamp is MySQL DATE, DATETIME, or TIMESTAMP.)

The outer query references rows returned by i, and performs a JOIN operation to retrieve the entire row from the table (aliased as g) to get the related Price.

To get the Name column from the Recipe table, we'd add a JOIN operation to that table...

SELECT g.RecipeID
     , r.Name
     , SUM(g.Price) AS `Total_Latest_Ingredient_Price`
  FROM Ingredient g
  JOIN (SELECT h.RecipeID
             , h.Name
             , MAX(h.Timestamp) AS `Timestamp`
          FROM Ingredient h
         GROUP
            BY h.RecipeID
             , h.Name
       ) i
    ON i.RecipeID  = g.RecipeID
   AND i.Name      = g.Name
   AND i.Timestamp = g.Timestamp
  JOIN Recipe r
    ON r.ID = g.RecipeID 
 GROUP BY g.RecipeID

Upvotes: 2

Evan Volgas
Evan Volgas

Reputation: 2911

VMai's query above is close, but you need to filter the latest date... so selecting the max timestamp for a given recipe/ingredient and join that back to your table. You should also be able do this with a WHERE clause, but the inner join to the Ingredient table (with max time stamp by recipe/ingredient) was more straightforward, so I just went with that

 SELECT
        I.RecipeID,
        R.Name,
        SUM(Price) AS Total_Ingredient_Price
    FROM
        Recipe R
    INNER JOIN
        Ingredient I
    ON
        R.ID = I.RecipeID
    INNER JOIN (SELECT MAX(Timestamp) AS latest, RecipeID, Name FROM Ingredient GROUP BY RecipieID, Name) AS c
    ON I.RecipeID = c.RecipeID
    AND I.Name = c.Name
    AND I.TimeStamp = c.latest
    GROUP BY
        I.RecipeID,
        R.Name

Upvotes: 1

VMai
VMai

Reputation: 10346

That's an easy one: You sum up the prices of the ingredients by recipe:

SELECT
    I.RecipeID,
    R.Name,
    SUM(Price) AS Total_Latest_Ingredient_Price
FROM
    Recipe R
INNER JOIN
    Ingredient I
ON
    R.ID = I.RecipeID
GROUP BY
    I.RecipeID,
    R.Name

Upvotes: 0

Related Questions