Sign in to follow this  

a big headache for an sql query....

This topic is 3718 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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

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

This topic is 3718 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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