SQL Query Help obtaining last order date from 2 tables

I have the following table

Orders_All
Account

Orders all contains every order line many thousand records and the 2 records are order_date and order_account_id.

This need to joi开发者_如何转开发n into the Account so other queries can be run as well but I want a report that shows the account_id and the last order date but only one record per account.

How can I create a query to acgieve this.

---

SELECT account_id, MAX(order_date) as last_order_date FROM Orders_All INNER JOIN Account ON order_account_id = account_id GROUP BY account_id

That will give you the account ID and the maximum (furthest in the future) date. The GROUP BY is what limits it - it's the maximum date "for each" account_id.

If an account has no orders and you still want that account to show, with a NULL in the date column, use a RIGHT OUTER JOIN instead of INNER JOIN there.

---

Try this:

Select accound_id,Max(OrderDate) from Order_All t1,AccountID t2
where t1.AccountID = t2.AccountID
group by account_ID

---

Subtitute * for the columns you want to show or leave it and try the next code.

select distinct * from Account A
join Orders_All O on O.order_account_id = A.account_id
--so far we have join both table, so we need the last order date from it
where o.order_date in (select MAX(order_date) from Orders_All)
--in where clause im getting max order date from table Orders_All

but what if I want last order date by customer?

Well then you could write this (not the best but it works):

select * 
  from Account A
  join (select account_id as Acc_id, MAX(order_date) as Date 
          from Orders_All 
         group by account_id) 
    as OD on A.account_id = OD.Acc_id