Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Update by Date help please

    Here is the code...
    How do I get the table just to give me back the date by latest date??

    update RIO.dbo.tblArCust
    SET CustName = LEFT (CustomerName,25),
    Addr1 = LEFT (Address1,25),
    Addr2 = LEFT (Address2,25),
    City = LEFT (COFFEE.dbo.vueCustomerAddress.City,15),
    Region = LEFT (State,2),
    PostalCode = LEFT (Zip,5)
    from COFFEE.dbo.vueCustomerAddress
    join RIO.dbo.tblArCust
    on COFFEE.dbo.vueCustomerAddress.CustomerCode = RIO.dbo.tblArCust.CustId
    AND COFFEE.dbo.vueCustomerAddress.addresstypeid = 1 and COFFEE.dbo.vueCustomerAddress.LastUpdatedDate > ????

    Any help would be great.

  2. #2
    Join Date
    Feb 2005
    Posts
    78
    Is this the sort of thing you are looking for?

    ...> (select max(COFFEE.dbo.vueCustomerAddress.LastUpdatedDate ) from COFFEE.dbo.vueCustomerAddress where ...)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a general solution to the problem you have. Use a subquery to isolate the latest record for each group of columns, and then use it to filter the original table:

    Code:
    SELECT  YOURTABLE.GROUPCOLUMNS,
            YOURTABLE.DATEFIELD,
            YOURTABLE.OTHERFIELDS
    FROM    YOURTABLE
            INNER JOIN --LATESTRECORDS
            (SELECT GROUPCOLUMNS,
                    MAX(DATEFIELD) AS DATEFIELD
            FROM YOURTABLE
                    GROUP BY GROUPCOLUMNS) AS LATESTRECORDS
            ON YOURTABLE.GROUPCOLUMNS = LATESTRECORDS.GROUPCOLUMNS
            AND YOURTABLE.DATEFIELD = LATESTCOLUMNS.DATEFIELD
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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