[thelist] multiple rows in the MySQL with same value

jft jft at worrigee.net
Thu Jul 31 09:01:03 CDT 2008


Nan,

Your question does not say which type/brand of database you are using, so in general terms ...

If you have to look at the most recent transaction on the table you have to have a way of identifying the most recent transaction.  If it is never possible to have more than one transaction on any day for a customer, then a transaction date field should suit your purpose, otherwise you may need to include either a timestamp field or an autoincrement sequence field on your table (the field should be indexed).

To access the field you could write ...
  select * 
  from MyTable m1 
    where m1.CustomerID = '123456'  
        and m1.SubscriptionNo = '45678'
        and m1.MyTimestampField = (select max(m2.MyTimestampField) 
                                                   from MyTable m2
                                                 where m2.CustomerNo = m1.CustomerNo
                                                     and m2.SubscriptionNo = m1.SubscriptionNo) 
   (ie get the full record where the timestamp matches the maximum timestamp on file for that customer) 
or you could write ...
   select * from MyTable where CustomerID = '123456' and SubscriptionNo = '45678'
   order by MyTimestampField desc limit 1
   (ie get all records for that customer, sort them in descending order & return only the first one in the sorted list).

Either of these statements should return either 1 record ( if a suitable candidate could be found) or 0 records (if there was no such record on file).

Cheers,
John

>  -------Original Message-------
>  From: Nan Harbison <nan at nanharbison.com>
>  Subject: [thelist] multiple rows in the MySQL with same value
>  Sent: 31 Jul '08 20:43
>  
>  Hi All,
>  
>  On a website I am doing, we are keeping track of every transaction. So a
>  person signs up, gets a subscription number from authorize.net If their
>  credit card expires, that transaction is recorded in the payment table with
>  the same subscription number. To check to see if their credit card has
>  expired, I have to look at their most recent transaction.
>  
>  So if I query the table for the customer ID and subsccription number, how
>  can know I am choosing the latest transaction (the transaction date is
>  recorded as well)?
>  
>  So the more general question I have is - when you do a query for a value
>  that is in a table more than once, does the query stop on the first one it
>  finds, so I should ORDER BY transaction date DESC? Or can you not count on
>  this? I can't seem to come up with the keywords to google this and get the
>  answer I am looking for.
>  
>  TIA
>  Nan
>  
>  --
>  
>  * * Please support the community that supports you.  * *
>  http://evolt.org/help_support_evolt/
>  
>  For unsubscribe and other options, including the Tip Harvester
>  and archives of thelist go to: http://lists.evolt.org
>  Workers of the Web, evolt !
>  



More information about the thelist mailing list