* Robert Citek > Is there a way to do a self-join and a group by in a single SQL query?
Yes, but I'm not sure if that is what you need... > 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. See the max-concat trick, described here: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > SELECT CustNo,MAX(CONCAT(OrderNo,'|',Amnt)) FROM PO GROUP BY CustNo; You may need to pad OrderNo with zeroes, if they are not all the same number of digits, and you can split the column in the SQL using string functions, see the example at the url above. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
