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

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.

Newbie ? Please Help!

I'm a little new at this and need some help. My assignment is to modify the following function and do the following steps:
1.If ab_Table.a_col = xy_Table.a_col, then dosomething.
??How do I populate xy_Table
??How does ab_Table get populated in the code below
??How do I compare the ab_Table.a_col to xy_Table.a_col to see if they match
??Where should the code go in the function

/*This is what already exists, I need to know where to put my code.*/
tbl_rc_ab_Table Number :=0

TYPE ab_Table_rectab IS TABLE OF ab_Table %ROWTYPE
INDEX BY BINARY INTEGER

tbl_ab_Table_rectab ab_Table_rectab;

FUNCTION fCreate(p_arg IN OUT...)
n NUMBER :=0
IF tbl_ab_Table_rectab.COUNT > 0 THEN
FOR n in 1..tbl_ab_Table_rectab.COUNT LOOP
IF tbl_ab_Table_rectab.cola = p_arg THEN
tbl_ab_Table_rectab.colb :=1
END IF;
END LOOP;
tbl_rc_ab_Table = tbl_ab_Table_rectab.COUNT + 1;
tbl_ab_Table_rectab(tbl_rc_ab_Table).col_c :=0
END IF:Answers to your questions:
Both #1 and #2 - populate a physical table with the INSERT statement.
#3 - Use the equivalence operator to test variables of same type.
#4 - Well, the "record group" you declare is only initialized (but no
values are being populated in it), the arg val ("p_arg) is of both IN OUT
(only IN is needed) and all this loop is doing right now is making
"colb" equal to a value of 1 if "cola" equals the "p_arg".

First, I think you'll need to place values into this RG (using a Cursor
from the table). Once the RG has values, now you'll be able to
get something out of it with your FOR loop.