Selecting the latest per group of items
hi there! i just joined this site and it seems to be very informative.
onto my question.
i have 2 tables products and cost
ProdCOde - PK
Effectivedate - PK
i tried this query:
SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail FROM cost a INNER JOIN product b USING (ProdCode)
WHERE EffectiveDate <= '2009-10-01'
GROUP BY a.ProdCode;
uhm yah its showing the right effectivedate but the cost on that specific effectivedate doesnt match.
so i want to select the latest date with the matching cost per item.
for example the date i selected is '2009-12-25' and the records for 1 item are these:
ProdCode EffectiveDate Cost
10000 2009-01-05 50
10000 2009-05-25 48
10000 2010-07-01 40
so in result i should get 10000 2009-05-25 48 because it is lesser than the date on my query and it is the latest for that item. and then i want to to show on my query the latest costs on each product.
hope to hear from you soon! thanks!
Welcome to DevX
What database are you using?
I ask because I'm not familiar with this keyword: USING
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
oh by the way im using MySQL
By mldardy in forum ASP.NET
Last Post: 12-09-2008, 02:44 PM
By Mocha in forum VB Classic
Last Post: 03-28-2008, 04:08 AM
By srinivasc_it in forum ASP.NET
Last Post: 09-11-2007, 06:01 AM
By Diego Vilar in forum Database
Last Post: 05-08-2001, 07:38 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL