CODEWITHSUNDEEP
sqlsql-order-by
Phil
Phil

Reputation: 11

SQL select query to order results based on the maximum value of an associated table

I have a customers table and an orders table.

Customers table has fields: id, name, status_id

Orders table has fields: id, customer_id, order_date

Each customer can have many orders.

I would like to know if there is an sql query which can select all the customers order by status_id and the last order_date of their orders.

So, the results would list first all the customers with status 1, and within those it would list them from the one whose most recent order is most recent to the one whose most recent order is least recent. Then it would list those with status 2 the same way and then status 3 the same way. In the whole list each customer should only appear once.

Eg.

customers table

id, name, status_id

1, john, 2  
2, jane, 1  
3, tim, 1

orders table

id, customer_id, date

1, 1, 2013-01-01  
2, 1, 2011-10-01  
3, 2, 2012-12-01  
4, 2, 2010-03-10  
5, 3, 2011-02-21  
6, 3, 2012-01-11

The query should give a list of customers like this

2 jane  
3 tim  
1 john

Tim and Jane are before john, because they have a status of 1. Jane is before Tim because her last order (2012-12-01) was more recent than Tim's (2012-01-11).

I tried this

SELECT customers.id, customers.name, customers.status_id
FROM customers
JOIN orders ON customers.id = orders.customer_id 
ORDER BY customers.status_id, orders.date

But some customers appear many times.

Thanks very much for your help, Phil

Upvotes: 1

Views: 987

Answers (2)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try the below Query

select cus.id as ID, cus.name as Name
from
customer cus
inner join order1 ord on cus.id=ord.customer_id
group by cus.id, cus.name,cus.status_id 
order by cus.status_id asc,max(ord.date)desc

SQL Fiddle

Upvotes: 2

Dharmesh Patel
Dharmesh Patel

Reputation: 1891

Try:

SELECT custome.id, customer.name FROM customer JOIN 
    `order` ON customer.id = `order`.customer_id GROUP BY customer.id ORDER BY customer.status, `order`.date DESC

Upvotes: -1

Related Questions