Sign in to follow this  
Jaguar_Lee

a big headache for an sql query....

Recommended Posts

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

Share this post


Link to post
Share on other sites
Moe    1256
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.

Share this post


Link to post
Share on other sites
Jaguar_Lee    114
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??

Share this post


Link to post
Share on other sites
ToohrVyk    1595
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`;

Share this post


Link to post
Share on other sites
Jaguar_Lee    114

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

Share this post


Link to post
Share on other sites
Jaguar_Lee    114
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!!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this