5

I have a table that contains Status information about Product items over time. Each row has a Modified DATETIME. I want to get the latest Status row, using the MODIFIED field, for each ProductNumber in one query. However the crux is that the MODIFIED field may contain duplicates, so when I join back onto ProductStatus mulitple records are returned.

It will be used in a VIEW and so I must able to have WHERE clause with "ProductNumber = 123" at the end.

Sample Data:

ID | DateCreated             | ProductNumber | Modified
====================================================================
1  | 2008-09-29 00:00:00.000 | 20070098      | 2014-10-10 20:22:59.467
2  | 2008-09-29 00:00:00.000 | 20070099      | 2014-11-10 20:22:59.467
3  | 2008-12-18 09:26:58.507 | 20070099      | 2014-12-10 20:22:59.467
4  | 2008-12-18 08:47:38.343 | 20070098      | 2014-10-10 20:22:59.467
6  | 2007-12-07 00:00:00.000 | 20070098      | 2014-10-10 20:22:59.467
5  | 2007-12-07 00:00:00.000 | 20070099      | 2014-02-10 20:22:59.467
11 | 2009-03-20 14:09:52.190 | 20070098      | 2014-10-10 20:22:59.467
34 | 2009-03-20 14:18:49.383 | 20070099      | 2014-10-10 20:22:59.467

SQL to create the data:

CREATE TABLE #ProductStatus ( ID INT, DateCreated DATETIME, ProductNumber INT, Modified DATETIME )

INSERT INTO #ProductStatus VALUES (1, '2008-09-29 00:00:00.000', 20070098, '2014-10-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (2, '2008-09-29 00:00:00.000', 20070099, '2014-11-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (3, '2008-12-18 09:26:58.507', 20070099, '2014-12-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (4, '2008-12-18 08:47:38.343', 20070098, '2014-10-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (6, '2007-12-07 00:00:00.000', 20070098, '2014-10-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (5, '2007-12-07 00:00:00.000', 20070099, '2014-02-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (11, '2009-03-20 14:09:52.190', 20070098, '2014-10-10 20:22:59.467')
INSERT INTO #ProductStatus VALUES (34, '2009-03-20 14:18:49.383', 20070099, '2014-10-10 20:22:59.467')

Getting the MAX Modified when Grouping by the ProductNumber,

SELECT ProductNumber, MAX(Modified) AS MaxModified
FROM #ProductStatus
GROUP BY ProductNumber

returns

ProductNumber | MaxModified
===========================
20070098      | 2014-10-10 20:22:59.467
20070099      | 2014-12-10 20:22:59.467

Based on this sample data the final recordset I am looking for is:

ID | DateCreated             | ProductNumber | Modified
====================================================================
1  | 2008-09-29 00:00:00.000 | 20070098      | 2014-10-10 20:22:59.467
3  | 2008-12-18 09:26:58.507 | 20070099      | 2014-12-10 20:22:59.467

Using an INNER JOIN and a TOP 1 to get the ID from ProductStatus based on the MaxModified and ProductNumber,

SELECT MainProductStatus.* 
FROM #ProductStatus MainProductStatus
INNER JOIN (
    SELECT TOP 1 LatestProductStatus.ID
    FROM #ProductStatus LatestProductStatus
    INNER JOIN (
        SELECT SubLatestProductStatus.ProductNumber, MAX(SubLatestProductStatus.Modified) AS MaxModified
        FROM #ProductStatus SubLatestProductStatus
        GROUP BY ProductNumber
    ) MaxProductStatus
        ON MaxProductStatus.ProductNumber = LatestProductStatus.ProductNumber
        AND MaxProductStatus.MaxModified = LatestProductStatus.Modified
        ORDER BY LatestProductStatus.DateCreated DESC
) AS ProductStatusLatestSubQuery ON ProductStatusLatestSubQuery.ID = MainProductStatus.ID

results in this recordset, getting the TOP 1 from all of the MAX items:

ID | DateCreated             | ProductNumber | Modified
====================================================================
11 | 2009-03-20 14:09:52.190 | 20070098      | 2014-10-10 20:22:59.467

I then looked into CROSS APPLY and OUTER APPLY further but am getting mixed results, e.g.

SELECT MainProductStatus.* , ProductStatusLatestSubQuery.*
FROM #ProductStatus MainProductStatus
CROSS APPLY (
    SELECT TOP 1 ID
    FROM #ProductStatus LatestProductStatus
    INNER JOIN (
        SELECT SubLatestProductStatus.ProductNumber, MAX(SubLatestProductStatus.Modified) AS MaxModified
        FROM #ProductStatus SubLatestProductStatus
        GROUP BY ProductNumber
    ) MaxProductStatus
        ON MaxProductStatus.ProductNumber = LatestProductStatus.ProductNumber
        AND MaxProductStatus.MaxModified = LatestProductStatus.Modified
    WHERE LatestProductStatus.ID = MainProductStatus.ID
    ORDER BY LatestProductStatus.DateCreated DESC
) AS ProductStatusLatestSubQuery

returns:

ID | DateCreated             | ProductNumber | Modified                | ID
===========================================================================
1  | 2008-09-29 00:00:00.000 | 20070098      | 2014-10-10 20:22:59.467 | 1
3  | 2008-12-18 09:26:58.507 | 20070099      | 2014-12-10 20:22:59.467 | 3
4  | 2008-12-18 08:47:38.343 | 20070098      | 2014-10-10 20:22:59.467 | 4
6  | 2007-12-07 00:00:00.000 | 20070098      | 2014-10-10 20:22:59.467 | 6
11 | 2009-03-20 14:09:52.190 | 20070098      | 2014-10-10 20:22:59.467 | 11

I'm really not sure why the CROSS APPLY isn't working as I expect. Maybe I need to get the DISTINCT ProductNumber records first to avoid extra-joining, but that doesn't help me get the end data.


I've checked out any similar items I could find on here before posting. This is my first question, feedback welcomed. TIA.

  • 1
    What makes ID 1 the correct row to be returned for 20070098, rather than 4, 6 or 11? – Mark Sinkinson May 10 '17 at 10:48
  • Sorry, I didn't state. When it has identical Modified and BusinessNumber I wanted to sub-order it. To be honest I'd want row number 11 which has the latest DateCreated, not row 11. – Keegan May 11 '17 at 7:29
2

If you're looking for MAX(Modified) field over ProductNumber, you can use ROW_NUMBER() function, and then get all rows where row number = 1.

WITH selMax AS
(
    SELECT ID, ProductNumber, DateCreated, Modified,
           ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY Modified DESC, 
                                                                  DateCreated DESC) RNum
    FROM   #ProductStatus
)
SELECT ID, ProductNumber, DateCreated, Modified
FROM   selMax
WHERE  RNum = 1
GO
ID | ProductNumber | DateCreated         | Modified           
-: | ------------: | :------------------ | :------------------
11 |      20070098 | 20/03/2009 14:09:52 | 10/10/2014 20:22:59
 3 |      20070099 | 18/12/2008 09:26:58 | 10/12/2014 20:22:59

Filtering by ProductNumber:

WITH selMax AS
(
    SELECT ID, ProductNumber, DateCreated, Modified,
           ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY Modified DESC, 
                                                                  DateCreated DESC) RNum
    FROM   #ProductStatus
    WHERE  ProductNumber = 20070098
)
SELECT ID, ProductNumber, DateCreated, Modified
FROM   selMax
WHERE  RNum = 1
GO
ID | ProductNumber | DateCreated         | Modified           
-: | ------------: | :------------------ | :------------------
11 |      20070098 | 20/03/2009 14:09:52 | 10/10/2014 20:22:59

dbfiddle here

  • To ensure consistency, I'd recommend adding an additional field to ROWNUMBER()'s ORDER BY. Based on sample output, currently it looks like ORDER BY Modified DESC, Id ASC might be right. Otherwise, there is a risk that (as the table is updated) it would return a different row in the future. as there are four possible rows with the same Modified value. – RDFozz May 10 '17 at 16:00
  • @RDFozz yes, you're right, but I was waiting for an answer to the comment on the question. – McNets May 10 '17 at 18:34
  • That makes very good sense. Still worth noting in case no response is forthcoming, or if someone else looks at this in the interim, but the final answer should be based on the actual requirements. – RDFozz May 10 '17 at 18:40
  • Fantastic, thank you. I needed to order the ROWNUMBER() by Modifed DESC, DateCreated DESC for my requirements but it works great out of the box. I am dealing with 400,585 rows in the real table and this runs the query in 0 seconds, so also a plus. – Keegan May 11 '17 at 7:25
  • I'm glad to be able to help. – McNets May 11 '17 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.