newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each foodType / recipeName pair.

Primary Key = foodType + recipeName + lastSaved

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

This should get you started. It will provide the lastest of each reciept variation.

SELECT
[FoodType],
[RecipeName],
max( [LastSaved] )
FROM [myRecipes]
GROUP BY
[FoodType],
[RecipeName]
ORDER BY
[FoodType],
[RecipeName]

This gets you the PK of each qualifying row, and then you could use it as a subquery or a JOIN derived table to get the remaining ingredients.

|||

Thanks, this really helps me out!

this is great...don't have to use a JOIN with this?

I'm under the impression subqueries as input to a parent query can only return one field?

I also did this:

SELECT FoodType,
recipeName,
LastSaved,
cupsOfSugar
FROM myRecipes q
WHERE cast(q.LastSaved as varchar(10)) in
(select MAX(cast(LastSaved as varchar(10)) ) from myRecipes
where FoodType= q.FoodType
and
recipeName= q.recipeName)

thanks again in advance,

bsierad

|||I was thinking as a sub-query in a WHERE clause to return the PK. Also, as a derived table for a JOIN.