开发者

SQL - confused with aggregate

I currently have this query:

select
  customers.emailaddress,
  MAX(orders.orderdate) as "last order"
from orders
join customers
  on orders.customerID = customers.customerID
group by customers.emailaddress

Which gives me the emails, and the last order date. With the 'Orders' table, there is a field named 'PaymentTotal', how can I get this based on the value returned by MAX(orders.orderdate)? (ie am trying to开发者_开发知识库 get the amount of the last order per email)


select c.EmailAddress, om.MaxOrderDate, o.PaymentTotal
from (
    select CustomerID, MAX(orders.orderdate) as MaxOrderDate
    from orders 
    group by CustomerID
) om
inner join orders o on om.CustomerID = o.CustomerID
    and om.MaxOrderDate = o.orderdate
inner join customers c on o.customerID = c.customerID      
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜