Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Unanswered: Transact SQL question

    I have a table called RETCHAIN. There are 5 columns in the table:

    1) Chain
    2) ItemNo
    3) Begin Date
    4) Retail $
    5) Units

    For a combination of Chain and ItemNo, there are multiple records (rows) with different Begin Date. Example:

    Chain ItemNo Begin Date Retail$ Units

    002 011-01013 1997-09-10 0.97 40.0
    003 011-01013 1997-09-10 0.97 40.0
    002 011-01013 1997-09-07 0.89 40.0
    002 011-01013 1997-09-03 0.58 40.0
    003 011-01013 1997-09-03 0.69 40.0
    002 011-01013 1997-08-20 0.97 40.0
    003 011-01013 1997-08-20 0.97 40.0

    As you can see from the sample data above, for Chain (002) and ItemNo (011-01013), there are multiple records with different Begin Date.

    Basically, I would like to select only those with the most recent Begin Date.
    So, for Chain (002) and ItemNo (011-01013), I would like to select only the record with Begin Date (1997-09-10). And for Chain (003) and ItemNo (011-01013), I would like to select only the record with Begin Date (1997-09-10).

    I have a couple thousand records to filter. Anybody knows how i can query for the result?

    Thanks!

    --Speedster

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    Code:
    SELECT MAX(Date) FROM RETCHAIN WHERE Chain = 002

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Maybe more like this?:

    select Chain,ItemNo,Begin Date,Retail,Units
    from retchain r join
    (select chain, max(begin date) as date from reetchain group by chain) a on r.chain = a.chain and r.begin date = a.date



    Hope this helps...

  4. #4
    Join Date
    Mar 2003
    Posts
    6
    Thanks, MCrowley. I tried using the query that you specified below and it works!!

    --Speedster123

    Originally posted by MCrowley
    Maybe more like this?:

    select Chain,ItemNo,Begin Date,Retail,Units
    from retchain r join
    (select chain, max(begin date) as date from reetchain group by chain) a on r.chain = a.chain and r.begin date = a.date



    Hope this helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •