Retrieve first record

kengreer · #1 (**permalink**) 12-18-03, 12:24

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

r123456 · #2 (**permalink**) 12-18-03, 12:48

select columns
from table t
where rowid = (select min(rowid) from table where t.key = key);

kengreer · #3 (**permalink**) 12-18-03, 13:03

Quote:

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)'.

r937 · #4 (**permalink**) 12-18-03, 13:27

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

kengreer · #5 (**permalink**) 12-18-03, 14:26

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech