CODEWITHSUNDEEP
sqlnetezza
Snoobie
Snoobie

Reputation: 193

Return Max Value Date for each group in Netezza SQL

+--------+---------+----------+------------+------------+
| CASEID | USER ID | TYPE     |   OPEN_DT  |  CLOSED_DT |
+--------+---------+----------+------------+------------+
|      1 |    1000 |  MA      | 2017-01-01 | 2017-01-07 |
|      2 |    1000 |  MB      | 2017-07-15 | 2017-07-22 |
|      3 |    1000 |  MA      | 2018-02-20 | NULL       |
|      8 |    1001 |  MB      | 2017-05-18 | 2018-02-18 |
|      9 |    1001 |  MA      | 2018-03-05 | 2018-04-01 |
|      7 |    1002 |  MA      | 2018-06-01 | 2018-07-01 |
+--------+---------+----------+------------+------------+

This is a snippet of my data set. I need a query that returns just the max(OPEN_DT) row for each USER_ID in Netezza SQL.

so given the above the results would be:

 | CASEID | USERID |  TYPE | OPEN_DT    |  CLOSED_DT |
 | 3      |  1000  |  MA   | 2018-02-20 | NULL       |
 | 9      |  1001  |  MA   | 2018-03-05 | 2018-04-01 |
 | 7      |  1002  |  MA   | 2018-06-01 | 2018-07-01 |

Any help is very much appreciated!

Upvotes: 1

Views: 2731

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use correlated subquery :

select t.*
from table t
where open_dt = (select max(t1.open_dt) from table t1 where t1.user_id = t.user_id);

You can also row_number() :

select t.*
from (select *, row_number() over (partition by user_id order by open_dt desc) as seq
      from table t
      ) t
where seq = 1;

However if you have a ties with open_dt then you would need to use limit clause with correlated subquery but i am not sure about the ties so i just leave it.

Upvotes: 4

Related Questions