CODEWITHSUNDEEP
sql
GNMO11
GNMO11

Reputation: 2259

SQL take lowest non zero value per group

I have data that looks like the following:

ID    DATE1    DATE2    DIFF
1    3-4-20    3-4-20    0
1    7-15-20   3-4-20    133
2    2-28-20   2-28-20   0
3    9-11-20   9-11-20   0
3    12-11-20  9-11-20   91
4    6-17-20   1-24-20   145 * Lowest non zero
4    6-17-20   6-17-20   0
5    5-15-20   2-19-20   86 *either work here
5    5-15-20   2-19-20   86
5    7-21-20   2-19-20   153
5    7-21-20   6-1-20    50 * Lowest non zero
5    9-25-20   2-19-20   219
5    9-25-20   6-1-20    116 * Lowest non zero
5    9-25-20   9-25-20   0
5    10-30-20  2-19-20   254
5    10-30-20  6-1-20    151
5    10-30-20  9-25-20   35 * Lowest non zero

What I would like to do is when an ID has multiple date1's take the lowest nonzero diff and accompanying date2 if it exists. So the output would look like:

ID    DATE1    DATE2    DIFF
1    3-4-20    3-4-20    0
1    7-15-20   3-4-20    133
2    2-28-20   2-28-20   0
3    9-11-20   9-11-20   0
3    12-11-20  9-11-20   91
4    6-17-20   1-24-20   145 
5    5-15-20   2-19-20   86 
5    7-21-20   6-1-20    50 
5    9-25-20   6-1-20    116 
5    10-30-20  9-25-20   35

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If I understand correctly, you can use row_number() with conditional logic for the ordering:

select t.*
from (select t.*,
             row_number() over (partition by id, date1
                                order by (case when diff <> 0 then 1 else 2 end),
                                         diff
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 2

Related Questions