Using CROSS APPLY with GROUP BY and TOP 1 with duplicate data

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

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

StackExchange Database Administrators Q#173183, answer score: 2