#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2006
    Posts
    20
    Rep Power
    0

    Last transaction query


    Hi all!

    I have a table with three fields, for all intents and purposes:

    Item
    XAction DateTime
    XAction Type

    There can be multiple xactions per item.

    I need a SQL that will give me the LAST transaction for each item.

    Now, if I am looking for ONE item, that is simple. But I need this for ALL items... basically I want the item, with the last xaction and the date time of that last transaction.

    Any ideas?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Code:
    SELECT t.Item
         , t.XActionDateTime
         , t.XActionType
      FROM ( SELECT Item
                  , MAX(XActionDateTime) AS latest
               FROM ThreeFieldTable
             GROUP
                 BY Item ) AS m
    INNER
      JOIN ThreeFieldTable AS t
        ON t.Item = m.Item
       AND t.XActionDateTime = m.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo