SQL Statement Help - Select Latest Order for Each Customer

SQL Statement Help - Select latest Order for each Customer

I don't think you do want to use MAX() as you don't want to group the OrderID. What you need is an ordered sub query with a SELECT TOP 1.

select * 
from Customers 
    inner join Orders 
        on Customers.CustomerID = Orders.CustomerID
        and OrderID = (
            SELECT TOP 1 subOrders.OrderID 
            FROM Orders subOrders 
            WHERE subOrders.CustomerID = Orders.CustomerID 
            ORDER BY subOrders.OrderDate DESC
        )

How to select last order date for each customer in mysql

Hi checkout my sql below..

select coalesce(max(o.order_datetime), '0000-00-00 00:00:00') as last_order_date, c.customer_id 
from table_orders as o
right join table_customers as c on o.customer_id = c.customer_id
group by c.customer_id
order by c.customer_id;

This might help you.

The sample results are given below.

How can I get Top 2 orders for each customer

This is my solution

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate desc) OrderNumber
        , *
    FROM #Order 
) EnumeratedOrders
WHERE OrderNumber <= 2

A detailed explanation about the ROW_NUMBER() function here

SQL: Get the most recent order from each customer

Here is the query part. I added a join with the ecs_goods table on the goods_id field and added goods_thumb field in the SELECT clause.

SELECT ecs_users.user_name, ecs_order_goods.goods_id,
ecs_order_goods.goods_name, order_info.add_time, order_info.consignee, ecs_goods.goods_thumb
FROM ecs_users, 
(
    SELECT order_id, user_id, add_time, consignee
    FROM ecs_order_info
    ORDER BY ecs_order_info.add_time DESC 
    LIMIT 0 , 20
) AS order_info, 
ecs_order_goods, ecs_goods
WHERE order_info.order_id = ecs_order_goods.order_id
AND order_info.user_id = ecs_users.user_id
AND ecs_goods.goods_id = ecs_order_goods.goods_id;

Get first and last Order and the highest value Item in each order for each Customer, all of which are separate tables

I think you need to keep in mind two main points with this type of query:

• The key to good performance with window functions is to not introduce an unnecessary sort. So while you can use ROW_NUMBER to get the first order in either direction, you should not use another opposing ROW_NUMBER to get the last. Rather use LEAD to check if the next row exists, thereby telling you if this is the last row. You can then use conditional aggregation.
• There are generally two ways to calculate first/last: a row-numbering solution, as above, or an APPLY, which picks out the exact one you need.
  
  I think that for the OrderDetails we should use an apply, because there are only two orders per customer that we need to find. This does need good indexing, so if OrderDetails is not well indexed, then you may want to switch to a row-numbering solution for this also.

select
    c.CustomerID,
    c.FirstName + ' ' + c.LastName as Name,
    cs.CustomerStatusDescription as Status,
    ct.CustomerTypeDescription as Type,
    pv.Volume80 as G3,
    o.FirstOrderID,
    o.FirstOrderDate,
    o.FirstSubTotal,
    o.FirstCountry,
    fod.ItemCode as FirstItemCode,
    fod.ItemDescription as FirstItemDescription,
    fopt.PriceTypeDescription as FirstPriceTypeDescription,
    o.LastOrderID,
    o.LastOrderDate,
    o.LastSubTotal,
    o.LastCountry,
    lod.ItemCode as LastItemCode,
    lod.ItemDescription as LastItemDescription,
    lopt.PriceTypeDescription as LastPriceTypeDescription 
from Customers c
left join CustomerTypes ct on ct.CustomerTypeID = c.CustomerTypeID
left join CustomerStatuses cs on cs.CustomerStatusID = c.CustomerStatusID
left join PeriodVolumes pv on pv.CustomerID = c.CustomerID
  and pv.PeriodTypeID = 2
  and pv.PeriodID = (
    select top 1 PeriodID
    from Periods p
    where p.PeriodTypeID = 2
      and p.StartDate <= @now
      and p.EndDate >= @now
  )
left join (
    select
      o.CustomerID,
      min(case when rn = 1 then OrderID end) as FirstOrderId,
      min(case when rn = 1 then OrderDate end) as FirstOrderDate,
      min(case when rn = 1 then SubTotal end) as FirstSubTotal,
      min(case when rn = 1 then Country end) as FirstCountry,
      min(case when nx is null then OrderID end) as LastOrderId,
      min(case when nx is null then OrderDate end) as LastOrderDate,
      min(case when nx is null then SubTotal end) as LastSubTotal,
      min(case when nx is null then Country end) as LastCountry,
      count(case when o.OrderDate >= DATEADD(month, -3, GETDATE()) then 1 end) as ThreeMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -3, GETDATE()) then BusinessVolumeTotal end) as ThreeMonthTotal,
      count(case when o.OrderDate >= DATEADD(month, -6, GETDATE()) then 1 end) as SixMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -6, GETDATE()) then BusinessVolumeTotal end) as SixMonthTotal,
      count(case when o.OrderDate >= DATEADD(month, -12, GETDATE()) then 1 end) as TwelveMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -12, GETDATE()) then BusinessVolumeTotal end) as TwelveMonthTotal
    from (
        select *,
            ROW_NUMBER() over (partition by o.CustomerID order by OrderDate) as rn,
            LEAD(OrderID) over (partition by o.CustomerID order by OrderDate) as nx
        from Orders o
        where o.OrderStatusID >= 7
          and o.OrderTypeID in (1,4,8,11)
          and o.OrderDate >= @timeAgo
    ) o
    group by o.CustomerID
) o on o.CustomerID = c.CustomerID
outer apply (
    select top 1
      od.ItemCode,
      od.ItemDescription
    from OrderDetails od
    order by od.BusinessVolume desc
    where od.OrderID = o.FirstOrderId
) fod
outer apply (
    select top 1
      od.ItemCode,
      od.ItemDescription
    from OrderDetails od
    order by od.BusinessVolume desc
    where od.OrderID = o.LastOrderId
) lod
left join PriceTypes fopt on fopt.PriceTypeID = o.FirstPriceTypeID 
left join PriceTypes lopt on lopt.PriceTypeID = o.LastPriceTypeID 
where c.CustomerStatusID in (1,2)
  and c.CustomerTypeID in (2,3);

I'm also going to give you a row-numbering version, as judging by your execution plan, it may actually be better. You need to try both

select
    c.CustomerID,
    c.FirstName + ' ' + c.LastName as Name,
    cs.CustomerStatusDescription as Status,
    ct.CustomerTypeDescription as Type,
    pv.Volume80 as G3,
    o.FirstOrderID,
    o.FirstOrderDate,
    o.FirstSubTotal,
    o.FirstCountry,
    o.FirstItemCode,
    o.FirstItemDescription,
    o.FirstPriceTypeDescription,
    o.LastOrderID,
    o.LastOrderDate,
    o.LastSubTotal,
    o.LastCountry,
    o.LastItemCode,
    o.LastItemDescription,
    o.LastPriceTypeDescription 
from Customers c
left join CustomerTypes ct on ct.CustomerTypeID = c.CustomerTypeID
left join CustomerStatuses cs on cs.CustomerStatusID = c.CustomerStatusID
left join PeriodVolumes pv on pv.CustomerID = c.CustomerID
  and pv.PeriodTypeID = 2
  and pv.PeriodID = (
    select top 1 PeriodID
    from Periods p
    where p.PeriodTypeID = 2
      and p.StartDate <= @now
      and p.EndDate >= @now
  )
left join (
    select
      o.CustomerID,
      min(case when rn = 1 then o.OrderID end) as FirstOrderId,
      min(case when rn = 1 then o.OrderDate end) as FirstOrderDate,
      min(case when rn = 1 then o.SubTotal end) as FirstSubTotal,
      min(case when rn = 1 then o.Country end) as FirstCountry,
      min(case when rn = 1 then od.ItemCode end) as FirstItemCode,
      min(case when rn = 1 then od.ItemDescription end) as FirstItemDescription,
      min(case when rn = 1 then opt.PriceTypeDescription end) as FirstPriceTypeDescription,
      min(case when nx is null then o.OrderID end) as LastOrderId,
      min(case when nx is null then o.OrderDate end) as LastOrderDate,
      min(case when nx is null then o.SubTotal end) as LastSubTotal,
      min(case when nx is null then o.Country end) as LastCountry,
      min(case when nx is null then od.ItemCode end) as LastItemCode,
      min(case when nx is null then od.ItemDescription end) as LastItemDescription,
      min(case when nx is null then opt.PriceTypeDescription end) as LastPriceTypeDescription,
      count(case when o.OrderDate >= DATEADD(month, -3, GETDATE()) then 1 end) as ThreeMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -3, GETDATE()) then BusinessVolumeTotal end) as ThreeMonthTotal,
      count(case when o.OrderDate >= DATEADD(month, -6, GETDATE()) then 1 end) as SixMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -6, GETDATE()) then BusinessVolumeTotal end) as SixMonthTotal,
      count(case when o.OrderDate >= DATEADD(month, -12, GETDATE()) then 1 end) as TwelveMonthCount,
      sum(case when o.OrderDate >= DATEADD(month, -12, GETDATE()) then BusinessVolumeTotal end) as TwelveMonthTotal
    from (
        select *,
            ROW_NUMBER() over (partition by o.CustomerID order by OrderDate) as rn,
            LEAD(OrderID) over (partition by o.CustomerID order by OrderDate) as nx
        from Orders o
        where o.OrderStatusID >= 7
          and o.OrderTypeID in (1,4,8,11)
          and o.OrderDate >= @timeAgo
    ) o
    left join PriceTypes opt on opt.PriceTypeID = o.PriceTypeID 
    join (
        select *,
          ROW_NUMBER() over (partition by od.OrderID order by od.BusinessVolume desc) as rn
        from OrderDetails od
    ) od on od.OrderID = o.OrderId
    where rn = 1 or nx is null
) o on o.CustomerID = c.CustomerID
where c.CustomerStatusID in (1,2)
  and c.CustomerTypeID in (2,3);

---

Good indexing is essential to good performance. I would expect roughly the following indexes on your tables, either clustered or non-clustered (clustered indexed INCLUDE every other column automatically), you can obviously add other INCLUDE columns if needed:

Customers (CustomerID) INCLUDE (FirstName, LastName)

CustomerTypes (CustomerTypeID) INCLUDE (CustomerTypeDescription)

CustomerStatuses (CustomerStatusID) INCLUDE (CustomerTypeDescription)

PeriodVolumes (CustomerID) INCLUDE (Volume80)

Periods (PeriodTypeID, StartDate, PeriodID) INCLUDE (EndDate)  -- can swap Start and End

Orders (CustomerID, OrderDate) INCLUDE (OrderStatusID, SubTotal, Country, BusinessVolumeTotal)

OrderDetails (OrderID, BusinessVolume) INCLUDE (ItemCode ItemDescription)

PriceTypes (PriceTypeID) INCLUDE (PriceTypeDescription)

You should think carefully about INNER vs LEFT joins, because the optimizer can more easily move around an INNER join.

Note also, that DISTINCT is not a function, it is calculated over an entire set of columns. Generally, one can assume that if a DISTINCT is in the query then the joins have not been thought through properly.

Sql return last order details for each customer

This is usually solved using window functions:

select *
from (
  select o.*, 
         row_number() over (partition by o.ClientID order by o.OrderDateTime desc) as rn
  from t_orders o
) t
where rn = 1
order by ClientId;

SQL Query to get value of recent order alongwith data from other tables

Instead of subquery in left join, you can check with outer apply.

Check following way

Select c.CustomerID, c.Name, s.SalePrice, RecentOrder.Date, RecentOrder.Amount 
from Customer c
LEFT JOIN Sales s ON c.CustomerID = s.ID
OUTER APPLY (
SELECT top 1 o.Date, o.Amount, o.CustomerID  
FROM [Order] o 
WHERE o.CustomerID = c.CustomerID ORDER BY o.Date DESC) RecentOrder`   

Select first purchase for each customer

You can simply treat the query you have come up with as an inner query. This will work on older version of SQL Server as well (you didn't specify version of SQL Server).

SELECT H.transaction_no, H.customer_id, H.operator_id, H.purchase_date
FROM Sales_Transactions_Header H
INNER JOIN
    (SELECT customer_id, MIN(purchase_date) As first_occurence
    FROM Sales_Transactions_Header
    GROUP BY customer_id) X
ON H.customer_id = X.customer_id AND H.purchase_date = X.first_occurence

---

Related Topics