Sql-server – Find Rows with the Minimum Date per Group

sql servert-sql

I have table like this and I need to remove duplicates and only leave the min date

+-----+-------------+-------+------+-------+-------+-------+
| ID  |    Date     | Alloc | Prod | Water |  Tub  |  Cas  |
+-----+-------------+-------+------+-------+-------+-------+
| 375 | 2011-10-10  |     0 |    0 |     0 | 14.7  | 0     |
| 375 | 2011-10-11  |     0 |    0 |     0 | 14.7  | 0     |
| 323 | 2014-04-24  |     0 |    0 |     0 | 352   | 555.7 |
| 323 | 2014-04-26  |     0 |    0 |     0 | 352   | 555.7 |
| 356 | 2014-04-01  |     0 |    0 |     0 | 318.8 | 471.1 |
| 356 | 2014-04-02  |     0 |    0 |     0 | 318.8 | 471.1 |
| 356 | 2014-04-03  |     0 |    0 |     0 | 318.8 | 471.1 |
| 356 | 2014-04-04  |     0 |    0 |     0 | 318.8 | 471.1 |
| 323 | 2014-05-20  |     0 |    0 |     0 | 649.1 | 976.9 |
| 323 | 2014-05-21  |     0 |    0 |     0 | 649.1 | 976.9 |
| 323 | 2014-05-22  |     0 |    0 |     0 | 649.1 | 976.9 |
+-----+-------------+-------+------+-------+-------+-------+

I would like my result set to be like this:

+-----+-------------------------+---+---+---+-------+-------+
| 375 | 2011-10-10 00:00:00.000 | 0 | 0 | 0 | 14.7  | 0     |
| 323 | 2014-04-24 00:00:00.000 | 0 | 0 | 0 | 352   | 555.7 |
| 356 | 2014-04-01 00:00:00.000 | 0 | 0 | 0 | 318.8 | 471.1 |
| 323 | 2014-05-20 00:00:00.000 | 0 | 0 | 0 | 649.1 | 976.9 |
+-----+-------------------------+---+---+---+-------+-------+

Any help will be greatly appreciated

SQLFiddle

Best Answer

If you are using SQL Server 2005 onward you can use the ROW_NUMBER() function:

WITH cte_GetLatest
AS
(
  SELECT ID, [Date], Alloc, Prod, Water, Tub, Cas,
         ROW_NUMBER() OVER (PARTITION BY ID,
                                         Alloc, 
                                         Prod, 
                                         Water, 
                                         Tub, 
                                         Cas 
                            ORDER BY Date) as rownumber
  FROM Example


)
SELECT *
FROM cte_GetLatest
WHERE rownumber = 1

Lothar

Related Question