Search code examples
sqlsql-server-2008group-byrow-number

In grouped result set, select latest row. If particular field in the latest row is 0 then select next row

CREATE TABLE TableOne(Name VARCHAR(10), Val1 INT, Val2 INT, Val3 INT, RDate DATE)

INSERT TableOne VALUES ('Rec01', 0, 0, 0, '11-Nov-2017'), ('Rec02', 0, 20, 30, '10-Nov-2017'), ('Rec03', 0, 0, 0, '09-Nov-2017'), ('Rec01', 10, 20, 30, '11-Nov-2017'), ('Rec02', 10, 20 ,30, '10-Nov-2017'), ('Rec03', 0, 0, 30, '08-Nov-2017'), ('Rec01', 0, 20, 0, '10-Nov-2017'), ('Rec02', 10, 20, 0, '09-Nov-2017'), ('Rec03', 10, 0, 30, '08-Nov-2017')

TableOne

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |   0  |   0  |   0  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
| Rec01  |  10  |  20  |  30  | 11-Nov-2017 |
| Rec02  |  10  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |  30  | 08-Nov-2017 |
| Rec01  |   0  |  20  |   0  | 10-Nov-2017 |
| Rec02  |  10  |  20  |   0  | 09-Nov-2017 |
| Rec03  |  10  |   0  |  30  | 08-Nov-2017 |
 -------------------------------------------

These records have to grouped by 'Name' and the latest record of each group need to be selected with following conditions

1. If a record has two rows with same date, select first row. 
2. If a record has two rows with same date but Val1, Val2, Val3 are 0 then select next row with same date

Expected result is

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |  10  |  20  |  30  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
 -------------------------------------------

I tried ROW_NUMBER() OVER PARTITION BY

SELECT Name, Val1, Val2, Val3, RDate FROM (
SELECT Name, Val1, Val2, Val3, Max(RDate) AS RDate, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY RDate DESC) AS Rno FROM TableOne GROUP BY Name, Val1, Val2, Val3, RDate) TempTable where TempTable.rno = 1

that returns

 -------------------------------------------
|  Name  | Val1 | Val2 | Val3 |    RDate    |
|-------------------------------------------|
| Rec01  |   0  |   0  |   0  | 11-Nov-2017 |
| Rec02  |   0  |  20  |  30  | 10-Nov-2017 |
| Rec03  |   0  |   0  |   0  | 09-Nov-2017 |
 -------------------------------------------

For Rec01, instead of first row, how I can select the next row with same date as Val1, Val2 and Val3 are 0?

Thanks

Solution

  • Try the below query

    SELECT  Name,Val1,Val2,Val3,RDate
FROM    (
    SELECT  *   
        ,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY RDATE DESC
            ,(CASE WHEN Val1 +Val2 +Val3 > 0 THEN 1 ELSE 0 END) DESC) AS SlNo
    FROM    TableOne
)   AS  T
WHERE   T.SlNo = 1

    Output:-

    Name    Val1    Val2    Val3    RDate
Rec01   10      20      30      2017-11-11
Rec02   0       20      30      2017-11-10
Rec03   0       0       0       2017-11-09