SQL most recent order? MS SQL

Question

Im just writing a query to look through my clients customers database and to list how many orders they have made etc.

What I'm struggling to add into this query is to only show me most recent OrderID for that email

Any ideas?

Here is my query

select top 1000 
     BuyerEMail
     ,COUNT(*) HowMany
     ,Name
from Orders 
where 
     Pay != 'PayPal'
group by 
     BuyerEmail
     ,Name
order by 
     HowMany Desc

Answer 1

If you are having troubles writing sql queries, try to break up your needs into single statements.

First you wanted the number of orders per buyer, which you already solved.

SELECT BuyerEMail
, Name
, COUNT(*) as TotalOrders
FROM Orders
WHERE Pay <> 'PayPal'
GROUP BY BuyerEmail, Name
Order By TotalOrders Desc

Now you wanted to display the latest order for each buyer. Something like this would do:

SELECT BuyerEMail
, Name
, MAX(OrderDate) LatestOrder
FROM Orders 
GROUP BY BuyerEmail, Name

Next, you need to combine your output to one statement. If you compare the two statements, both are grouped by the same set (Buyer and Name), so you could sum it up to:

SELECT BuyerEMail
, Name
, COUNT(*) as TotalOrders
, MAX(OrderDate) LatestOrder
FROM Orders
WHERE Pay <> 'PayPal'
GROUP BY BuyerEmail, Name

If you only want to count the orders having Pay != 'PayPal', you could do:

SELECT BuyerEMail
, Name
, COUNT(CASE WHEN Pay != 'PayPal' THEN 1 END) as TotalOrders
, MAX(OrderDate) LatestOrder
FROM Orders
GROUP BY BuyerEmail, Name

Now you commented you would also want the OrderID for the latest Order. A Lead() function in sqlserver 2012+ could do, a subselect or how I prefer a cross apply:

SELECT o.*
, OrderID as LastOrderID
, OrderDate as LastOrderDate
FROM (
    SELECT BuyerEMail
    , Name
    , COUNT(*) as TotalOrders
    FROM Orders
    WHERE Pay != 'PayPal'
    GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders s
    WHERE s.BuyerEmail = o.BuyerEmail
    ORDER BY OrderDate DESC
) ca

As you can see, things become easier if you split it up in smaller logical parts.