Selecting the latest per group of items


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Selecting the latest per group of items

  1. #1
    Join Date
    Feb 2010
    Posts
    2

    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

    PRODUCT
    ProdCOde - PK
    ProdName

    COST
    Effectivedate - PK
    RetailCOst
    Prodcode

    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!

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    oh by the way im using MySQL

Similar Threads

  1. Replies: 8
    Last Post: 12-09-2008, 02:44 PM
  2. Replies: 2
    Last Post: 03-28-2008, 04:08 AM
  3. Replies: 3
    Last Post: 09-11-2007, 06:01 AM
  4. selecting the latest rows in a table
    By Diego Vilar in forum Database
    Replies: 1
    Last Post: 05-08-2001, 07:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center