MAX Date according to join

Transact-SQL
1

This sql is returning too many rows because each OrderID has multiple OrderItemID's.
With each OrderItemID, there is a different datetime.

SELECT o.OrderID, oi.OrderItemID, MAX( r.SubmittedComplete ) AS SubmittedComplete
FROM tblOrder o 
LEFT JOIN tblOrderItem oi  ON oi.OrderID = o.OrderID
LEFT JOIN tblRun r  ON r.OrderItemID = oi.OrderItemID
GROUP BY o.OrderID, oi.OrderItemID, r.SubmittedComplete
ORDER BY o.OrderID DESC

For instance:

357626    1039704    2015-05-15 08:57:02.457
357626    1039706    2015-05-15 08:57:02.663
357626    1039705    2015-05-15 08:57:02.573
357626    1039707    2015-05-15 08:57:02.740
357626    1039708    2015-05-15 08:57:02.823
357625    1039696    2015-05-15 08:37:27.983
357625    1039697    2015-05-15 08:37:28.047
357625    1039698    2015-05-15 08:37:28.123

It should only bring back DISTINCT OrderID's with a MAX SubmittedComplete per OrderItemID.

For instance it should bring back only:

357626    1039708    2015-05-15 08:57:02.823
357625    1039698    2015-05-15 08:37:28.123

I've tried a few ways to get this to work right but now I need some opinions.

Thanks!!!

2

Here is one way.

SELECT  o.OrderID ,
        oi.OrderItemID ,
        r.SubmittedComplete
FROM    tblOrder o
       OUTER APPLY
       (
			SELECT TOP (1) OrderItemID
			FROM tblOrderItem oi
			WHERE oi.OrderID = o.OrderID
       ) oi
        OUTER APPLY
        (
			SELECT TOP (1) SubmittedComplete
			FROM tblRun r
			WHERE r.OrderItemID = oi.OrderItemID
			ORDER BY SubmittedComplete DESC
		) r
ORDER BY o.OrderID DESC;
3

[quote="Zath, post:1, topic:1585"]
SELECT o.OrderID, oi.OrderItemID, MAX( r.SubmittedComplete ) AS SubmittedComplete
FROM tblOrder o
LEFT JOIN tblOrderItem oi ON oi.OrderID = o.OrderID
LEFT JOIN tblRun r ON r.OrderItemID = oi.OrderItemID
GROUP BY o.OrderID, oi.OrderItemID, r.SubmittedComplete
ORDER BY o.OrderID DESC
[/quote]Remove the highlighted text.

4

James,

Thanks so much for your suggestion. It worked like a charm!!
I think I need to refresh my memory when using outer apply.

5

If you have several rows in tblOrderItem for a given OrderID, and each of those could have several rows in tblRun, and if you are trying to get the maximum date out of all of those, what I posted may not be logically correct. If that is the case, you may need something like shown below

SELECT  o.OrderID ,
        x.OrderItemID ,
        x.SubmittedComplete
FROM    tblOrder o
		OUTER APPLY
		(
			SELECT TOP (1)
				oi.OrderItemID,
				r.SubmittedComplete
			FROM
				tblOrderItem oi
				INNER JOIN tblRun r ON
					r.OrderItemID = oi.OrderItemID
			ORDER BY
				SubmittedComplete DESC
		) x
ORDER BY o.OrderID DESC;
6

Thanks for the update!