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 unique recipe.

[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

select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar
FROM myRecipes Order by cupsOfSugar DESC --Returns greatest
UNION
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar

FROM myRecipes Order by lastSaved -- Returns latest

PS. Best forum for this question is Transact-SQL
|||

Thanks,

but, doesn't this only return one row?

I'm looking for:

For each unique foodType and recipeName, please return all the fields in my table, and, if there are any duplicate records with foodType and recipeName, please only return that record whose lastSaved field is the max for that particular set.

This table basically holds a history of all saved recipes created by the user, but he/she should only ever see the latest and greatest...

PS: The primary key on this table is foodType + recipeName + lastSaved

thanks again in advance,

ben

|||Check my response in TransactSQL