Hello all,
Is there a way to do a self-join and a group by in a single SQL query?
For example, I have an PurchaseOrder table (see below) and would like to know what was the most recent order-number for each customer and the amount of that order. So far, I have been able to do this in two steps:
1) create a temporary table of the group by data
2) join the new table with the original
Thanks in advance for any pointers or URLs to on-line docs explaining how to do this.
Regards, - Robert
-----
$ cat <<! | mysql -t create temporary table PO (OrderNo int, CustNo int, Amnt int); insert into PO values (101,1001,20) ; insert into PO values (102,1001,25) ; insert into PO values (103,1001,40) ; insert into PO values (101,1002,20) ; insert into PO values (102,1002,30) ; insert into PO values (101,1003,25) ; insert into PO values (102,1003,55) ; select * from PO;
create temporary table PO1 select max(OrderNo) as "OrderNo", CustNo from PO group by CustNo;
select PO1.OrderNo, PO1.CustNo, PO.Amnt from PO1, PO where PO1.OrderNO = PO.OrderNO and PO1.CustNo = PO.CustNo;
!
+---------+--------+------+ | OrderNo | CustNo | Amnt | +---------+--------+------+ | 101 | 1001 | 20 | | 102 | 1001 | 25 | | 103 | 1001 | 40 | | 101 | 1002 | 20 | | 102 | 1002 | 30 | | 101 | 1003 | 25 | | 102 | 1003 | 55 | +---------+--------+------+ +---------+--------+------+ | OrderNo | CustNo | Amnt | +---------+--------+------+ | 103 | 1001 | 40 | | 102 | 1002 | 30 | | 102 | 1003 | 55 | +---------+--------+------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
