🔒 Locked

a big headache for an sql query....

Started by Jaguar_Lee Oct 10, 2007 at 5:47 AM 7 replies 1.3k views

Original Post

Oct 10, 2007 at 5:47 AM

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!!

HumanoidTyphoon

Oct 10, 2007 at 6:27 AM

Try Max(OrderID) ?

that should only return the last order

Moe

Oct 10, 2007 at 10:33 AM

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.

ju2wheels

Oct 10, 2007 at 11:11 AM

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.

Jaguar_Lee

Oct 12, 2007 at 2:38 AM

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??

ToohrVyk

Oct 12, 2007 at 3:32 AM

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`;

Jaguar_Lee

Oct 12, 2007 at 3:43 AM

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

Jaguar_Lee

Oct 12, 2007 at 3:44 AM

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!!

Jaguar_Lee

Oct 12, 2007 at 3:49 AM

oh, another thing, i think we have to use "left join", because even though the client has no order, he must be displayed also

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.