CODEWITHSUNDEEP
mysqlsql-server
Michael Scarpa
Michael Scarpa

Reputation: 121

SQL take max of similar id's from join

I'm fairly new to more advanced SQL queries

Given the following tables and associated fields:

Person PersonId, FirstName, LastName

Order OrderId, PersonId, OrderDateTime

I want to write a query that will join both tables by PersonId and will retrieve every person and their most recent order.

So if James Doe (PersonId = 1) below has many orders in the orders table,

OrderId, PersonId, OrderDateTime

1          1          12/1/2013 9:01 AM

2          1          2/1/2011 5:01 AM

3          2          10/1/2010 1:10 AM

it will only take the most recent for his.

PersonId NameFirst NameLast OrderId OrderDateTime 

1 James Doe 1    12/1/2013 9:01 AM 

2 John Doe 3     10/1/2010 1:10 AM

I have been trying something like this

SELECT p.PersonID, o.OrderID, MAX(o.OrderDateTime) From Person p
JOIN Orders o ON p.PersonID = o.PersonID
GROUP BY p.PersonID,

Thanks

Upvotes: 2

Views: 76

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

You can use variables to simulate ROW_NUMBER not available in MySQL:

SELECT p.PersonId, FirstName, LastName, 
       o.OrderId, o.OrderDateTime
FROM Person AS p
LEFT JOIN (
  SELECT OrderId, OrderDateTime, PersonId,
         @row_number := IF(@pid <> PersonId,
                           IF(@pid:=PersonId, 1, 1),
                           IF(@pid:=PersonId, @row_number+1, @row_number+1)) AS rn
  FROM `Order`
  CROSS JOIN (SELECT @row_number := 0, @pid := 0) vars
  ORDER BY OrderDateTime DESC
) AS o  ON p.PersonId = o.PersonId AND o.rn = 1

rn = 1 for the top level record within each PersonId slice of the derived table. Using this predicate in the ON clause of the LEFT JOIN we can match each row of Person to the most recent row of Order and obtain all Order fields.

Demo here

EDIT:

In SQL-Server the query looks like this:

SELECT p.*, o.OrderId, o.OrderDateTime
FROM Person AS p
LEFT JOIN (
   SELECT *, 
          ROW_NUMBER() OVER (PARTITION BY PersonId 
                             ORDER BY OrderDateTime DESC) AS rn
   FROM [Order]
) AS o ON p.PersonId = o.PersonId AND o.rn = 1

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

The inner query in this solution is a temporary table containing the most recent orders for each person. I join this back to the Orders table to get the fields you want, and then join again to the Person table.

SELECT p.PersonID, p.NameFirst, p.NameLast, o.OrderID, o.OrderDateTime
FROM Person p INNER JOIN Orders o
ON o.PersonId = p.PersonId
INNER JOIN
(
    SELECT o1.PersonId, MAX(o1.OrderDateTime) AS maxTime
    FROM Orders o1
    GROUP BY o1.PersonId
) t
ON o.PersonId = t.PersonId AND o.OrderDateTime = t.maxTime

Upvotes: 3

Kritner
Kritner

Reputation: 13765

So the most recent order per every person. You could do something like this:

select p.*, o.*
from person p
inner join orders o on p.personId = o.personId
inner join (
    -- get the max order per person
    SELECT max(orderId) as orderId, personId
    from orders
    group by personId
) maxOrder on o.orderId = maxOrder.orderId

joining o onto maxOrder on the orderId filters the result set only to the orders that are also the maximum order per customer.

Note that I used the ID on the table rather than the datetime, as the ID is guaranteed to be unique (for help with joins) - this is not always available depending on the nature of the use in the table, but it looks like it should work for your case.

Upvotes: 0

Related Questions