[EMAIL PROTECTED] schrieb:
I have the following table:
explain domain_payments;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| Invid | int(11) | | | 0 | |
| Custid | int(11) | | | 0 | |
| Date | date | YES | | NULL | |
| Description | varchar(80) | YES | | NULL | |
| Domain | varchar(150) | YES | | NULL | |
| UnitPrice | double | YES | | NULL | |
| Quantity | int(11) | | | 0 | |
| Amount | double | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
My goal is to create a table with the latest payment date and invoice id
for each domain. This is what I did:
create table t select domain,max(invid) as invid
from domain_payments group by domain;
alter table t add date_paid date;
update t,domain_payments as tr set date_paid=date where t.invid=tr.invid;
The above worked fine for the number of records in my dataset. In
learning [my]sql I am trying to see how this would best be done with a
large dataset as well more efficiently in general.
Thanks for any pointers.
CREATE TABLE `t`
SELECT `Domain`, `Invid`, `Date`
FROM `domain_payments`
WHERE `domain_payments`.`Invid` IN (
SELECT MAX(`Invid`) FROM `domain_payments` GROUP BY `Domain`)
you can also save this as a VIEW
btw. `Domain` should have an index, and i think `Invid` and `Custid` too
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
