Newbie needs help :) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Newbie needs help :)

Author

Topic

bszom
Starting Member

9 Posts

Posted - 2006-05-19 : 08:39:42

Please help me with the following, it would be much appreciated!

As part of a small DB I've got two tables, one called ingredient, the other pricechange.
Pricechange tracks the changes in price for the ingredients.

What I'm trying to do is to select only the ingredient id, name, and price from these two tables.
What I've got so far is:

SELECT i.ingred_id AS [Ingredient ID], i.igname AS [Ingredient Name], pc.price AS [Price]
FROM ingredient AS i INNER JOIN pricechange AS pc
ON i.ingred_id = pc.ingred_id

which works except that where there has been more than one change in price I obviously get multiple rows for each ingredient.
But I only want/need the latest price. The column for the date of the change in pricechange is startdate.
I've tried using MAX(startdate) and other things, but the question remains:
Is there any way of getting the ingredient id, its name and only the latest price from these two tables without selecting the date column? ie: I don't want a date in my result set.

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-05-19 : 08:55:37

Something like this:


SELECT i.ingred_id AS [Ingredient ID], i.igname AS [Ingredient Name], pc.price AS [Price]
FROM ingredient AS i 
     JOIN
     (
         SELECT ingred_id, MAX(startdate) AS MAX_startdate
         FROM   pricechange
--       WHERE  ... Duplicate any WHERE clause from main statement if necessary
     ) AS MAX_PC
         ON i.ingred_id = MAX_PC.ingred_id
     INNER JOIN pricechange AS pc
         ON i.ingred_id = pc.ingred_id
        AND MAX_PC.MAX_startdate = pc.startdate

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-19 : 08:57:11

[code]SELECT i.ingred_id AS [Ingredient ID], i.igname AS [Ingredient Name], pc.price AS [Price]
FROM ingredient AS i INNER JOIN pricechange AS pc
ON i.ingred_id = pc.ingred_id
AND pc.startdate = (select max(startdate) from pricechange x where x.ingred_id = pc.ingred_id)[/code]

bszom
Starting Member

9 Posts

Posted - 2006-05-19 : 09:08:31

Thank you both of you. It's so simple I can't believe I overlooked it! :) Then again I am a noob in SQL.

Kristen
Test

22859 Posts

Posted - 2006-05-19 : 09:14:04

"It's so simple I can't believe I overlooked it"

Nah, its a question of thinking Set-Based, and that takes a while - well it did in my case!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources