Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Help with getting row with latest date

    guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?

    Ex:

    TransactionID | Customer ID | TrasanctionDate

    1 | 2 | 9/3/2007 12:00:00 AM
    2 | 3 | 9/4/2007 12:00:00 AM
    3 | 2 | 9/5/2007 12:00:00 AM
    4 | 2 | 9/6/2007 12:00:00 AM

    Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2

    4 | 2 | 9/6/2007 12:00:00 AM

    Any help will be greatly appreciated. Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Select Trans.TransactionID,
    Trans.CustomerID,
    Trans.TrasanctionDate
    From Trans
    Inner join
    (Select CustomerID,
    MAx(TrasanctionDate) MaxTranDate
    from Trans
    group by CustomerID
    ) MaxTran
    On Trans.CustomerID = MaxTran.CustomerID
    And Trans.TrasanctionDate= MaxTran.MAxTranDate
    Where TRans.CustomerID = 2
    Cheers....

    baburajv

  3. #3
    Join Date
    Sep 2005
    Posts
    74
    Thanks a lot!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another way:
    Code:
    SELECT TransactionID
         , CustomerID
         , TrasanctionDate 
      FROM Trans as T
     WHERE TrasanctionDate = 
           ( SELECT MAX(TrasanctionDate)
               FROM Trans
              WHERE CustomerID = T.CustomerID )
    this way is easier to read and understand, i believe

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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