a big headache for an sql query....
Author
for example we have table : customers, orders.
now, we have to list all the customers, with their lastest orders(if he has any orders)
it must be done in one sql query.
at first i use "left join", but if the customer has many orders, then he would be displayed many times...
then i add a "distinct", but... i can only get the oldest orders not the lastest...
does anyone have idea for that?? Thanks!!
Is this a homework question?
Select Customer.CustomerName, Customer.CustomerID, Order.OrderDate from Customer inner join Order where Order.CustomerID = Customer.CustomerID Order By Order.OrderDate
It's been a while since I have done any real SQL, but that's how I remember doing it.
Select Customer.CustomerName, Customer.CustomerID, Order.OrderDate from Customer inner join Order where Order.CustomerID = Customer.CustomerID Order By Order.OrderDate
It's been a while since I have done any real SQL, but that's how I remember doing it.
Group by customer id and pull the one with max order id (assuming the latest would have the max) or use the date of order if provided.
Author
Quote:Original post by ju2wheels
Group by customer id and pull the one with max order id (assuming the latest would have the max) or use the date of order if provided.
yes... but how??
My SQL is notoriously ugly, but I would suggest to use the following to get the latest order id for each customer:
And so, to get the details for it:
SELECT MAX(`id`) FROM `table` GROUP BY `customer`;And so, to get the details for it:
SELECT customer, order FROM `table`INNER JOIN ( SELECT MAX(`id`) AS `maxid` FROM `table` GROUP BY `customer`)ON `maxid`= `id`;
Author
select client.*, order.* from client left join order on order.client_id=client.id and order.id = (select max(order.id) from order where order.client_id=client.id)
and that's ok
it doesn't run very slow
lol
Author
Quote:Original post by ToohrVyk
My SQL is notoriously ugly, but I would suggest to use the following to get the latest order id for each customer:SELECT MAX(`id`) FROM `table` GROUP BY `customer`;
And so, to get the details for it:SELECT customer, order FROM `table`INNER JOIN ( SELECT MAX(`id`) AS `maxid` FROM `table` GROUP BY `customer`)ON `maxid`= `id`;
yes!! Thank you anyway!!
Author
oh, another thing, i think we have to use "left join", because even though the client has no order, he must be displayed also
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
