[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