Need help exporting a database with most recently entered rows.

[project42]

I have a 2 tables, customers and orders. I'm using phpMyAdmin and I need to do a SQL query that will output the database with the customers database but it needs to only display row with the date of the most recent order.

Heres a variation that I've been trying and it's still not coming out correctly.

SELECT DISTINCT orders.user_id, customers.*
FROM orders, customers
WHERE customers.id = orders.user_id
GROUP BY orders.id

I'm not using PHP to output this either. I'm just trying to output it in phpMyAdmin so I can then export the data.

Any help is appreciated.

[bogocles]

Try this out ... (you'll need to replace `ORDER_DATE` with whatever the field name is for you date column in `orders`)


CREATE TEMPORARY TABLE `latest_customer_orders`(
`customer_id` INT NOT NULL,
`last_order_date` DATETIME NOT NULL
);

-- Make inserts into the temp table

INSERT INTO
`latest_customer_orders`
SELECT
`customers`.`id`,
MAX(`order`.`ORDER_DATE`)
FROM
`customers` INNER JOIN `orders` ON `customers`.`id` = `orders`.`user_id`
GROUP BY
`customers`.`id`;

-- Now output the dataset you are looking for

SELECT
`customers`.*,
`tmp`.`last_order_date`
FROM
`customers`
INNER JOIN `latest_customer_orders` AS `tmp`
ON `customers`.`id` = `tmp`.`customer_id`;



I used a temporary table to map an aggregate (max order date) to customer ID's to make things a bit easier.

[project42]

threw an error...

#1054 - Unknown column 'tmp.customer_id ' in 'on clause'