Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Retrieve first record

    I have a table of payments with these columns:

    PaymentDate,UID,Item,Amount,Status,TransactionID

    I'd like to retrieve the first instance of each purchase by a user (UID) where Status is "Completed".

    For example

    1/1/2003,10,Socks,7,Pending,1001
    1/1/2003,10,Socks,7,Completed,1002
    1/2/2003,11,Shirt,20,Pending,1003
    1/2/2003,11,Shirt,20,Completed,1004
    1/3/2003,11,Pants,30,Completed,1005

    should return:

    1/1/2003,10,Socks,7,Completed,1002
    1/2/2003,11,Shirt,20,Completed,1004

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select columns
    from table t
    where rowid = (select min(rowid) from table where t.key = key);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    Originally posted by r123456
    select columns
    from table t
    where rowid = (select min(rowid) from table where t.key = key);
    Thanks for the reply but forgive me for being a newbie. I tried

    SELECT ID,PaymentDate,UID,AID,ItemNumber,Username,Payment Status,TxnID FROM transactions where UID = (select min(UID) from table where transactions.UID = UID)

    and I get

    [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'UID = (select min(UID) from table where transactions.UID = UID)'.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select PaymentDate
         , UID
         , Item
         , Amount
         , Status
         , TransactionID
      from transactions   FOO
     where Status  = 'Completed'
       and PaymentDate
         = ( select min(PaymentDate)
               from transactions
              where Status  = 'Completed'
                and UID = FOO.UID )
    rudy
    http://r937.com

  5. #5
    Join Date
    Dec 2003
    Posts
    3
    Originally posted by r937
    Code:
    select PaymentDate
         , UID
         , Item
         , Amount
         , Status
         , TransactionID
      from transactions   FOO
     where Status  = 'Completed'
       and PaymentDate
         = ( select min(PaymentDate)
               from transactions
              where Status  = 'Completed'
                and UID = FOO.UID )
    rudy
    http://r937.com
    It's a winner! Thank you!

    I thought this could be done with some form of TOP and/or GROUP BY but I spent all morning trying different incarnations with no luck. Thanks again for your help.

Posting Permissions

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