Budyn Budyn - 6 months ago 13
SQL Question

SQL most recent order? MS SQL

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

Give this a go;

SELECT TOP 1000 
o.BuyerEMail
,COUNT(*) HowMany
,o.Name
,o2.OrderID
FROM Orders o
JOIN 
    (
     SELECT 
     BuyerEmail
     ,MAX(OrderDate) Latest 
     FROM Orders 
     GROUP BY BuyerEmail
    ) l
ON o.BuyerEmail = l.BuyerEmail

JOIN Orders o2
ON l.BuyerEmail = o2.BuyerEmail
AND l.OrderDate = o2.OrderDate

WHERE Pay != 'PayPal'

GROUP BY 
    o.BuyerEmail
    ,o.Name
    ,l.Latest
ORDER BY 
    COUNT(*) DESC

It works out the latest order by each email address in a sub query, you can then use this in the SELECT. I've also aliased the tables to make things easier.

You can do this another way too, by nesting subqueries;

SELECT TOP 1000 
o.BuyerEMail
,COUNT(*) HowMany
,o.Name
,o2.OrderID
FROM Orders o
JOIN 
    (
     SELECT
     BuyerEmail
     ,OrderID
     FROM
     Orders ord
     JOIN
        (
            SELECT 
            BuyerEmail
            ,MAX(OrderDate) Latest 
            FROM Orders 
            GROUP BY BuyerEmail
        ) ma
     ON ord.BuyerEmail = ma.BuyerEmail
     AND ord.OrderDate = ma.OrderDate
    ) l
ON o.BuyerEmail = l.BuyerEmail

WHERE Pay != 'PayPal'

GROUP BY 
    o.BuyerEmail
    ,o.Name
    ,l.Latest
ORDER BY 
    COUNT(*) DESC
Comments