Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Texas
    Posts
    5

    Unanswered: Trying to get multiiple max records and having problems...

    I have a table that has multiple records for each reference number. I am trying to select

    the max dateUpdated record for each reference number. The problem I am having is that my max

    dateUpdated is different for each reference number and the other fields within the record may

    have unique data so the select statement may pull more than 1 record for the same reference number because the whole record is not distinct... This statement works but only if the data is the same in all records except referencenum and dateupdated.....
    SELECT DISTINCTROW [ReferenceNum], [Description], [Type], max([DateUpdated]) AS LastUpdated
    FROM ProjectTestTable
    GROUP BY [ReferenceNum], [Description], [Type]
    HAVING (max([DateUpdated]))
    WITH OWNERACCESS OPTION;
    Someone please HELP..... All I want to pull is the whole record that has a the last updated

    date for each referencenum.... Thanks, Shawnash

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a correlated subquery will do it
    Code:
    select [ReferenceNum]
         , [Description]
         , [Type]
         , [DateUpdated]
      from ProjectTestTable xx
     where [DateUpdated]
           = ( select max([DateUpdated])
                 from ProjectTestTable 
                where [ReferenceNum] = xx.[ReferenceNum] )
    the subquery selects the latest date for each ReferenceNum, and that's the row that is selected by the outer query

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Nov 2002
    Location
    Texas
    Posts
    5
    Thank you - I will try it tomorrow...

  4. #4
    Join Date
    Nov 2002
    Location
    Texas
    Posts
    5

    Thank you sooooooo much...

    Hi Rudy...
    I just wanted to thank you soooooooo much for your response to my message. I tried it this morning and it worked wonderfully!! You're my hero!!
    Thanks again, Shawn

Posting Permissions

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