sql - Need help to make correct select query in DB2 -

i haven't done yet relevant course @ university can't sure query correct, works looks weird need review , feedback.

i've following ip_table table mixed primary key: id + ip

---------------------------------- |  id  | date      | ip          | ---------------------------------- |  1   | 2016-10-01| 1.2.3.10    | ---------------------------------- |  2   | 2016-10-20| 1.2.3.20    | ---------------------------------- |  2   | 2016-10-25| 1.2.3.40    | ---------------------------------- |  3   | 2016-10-01| 1.2.3.10    | ---------------------------------- |  3   | 2016-10-25| 1.2.3.25    | ---------------------------------- 

i want rows data has maximum value among same ids, following:

---------------------------------- |  id  | date      | ip          | ---------------------------------- |  1   | 2016-10-01| 1.2.3.10    | ---------------------------------- |  2   | 2016-10-25| 1.2.3.40    | ---------------------------------- |  3   | 2016-10-25| 1.2.3.25    | ---------------------------------- 

right have 2 queries job:

select * (select id, max(date) last_date                     ip_table                  group id                     order max(date) asc) t1             left join ip_table t2                 on t1.id = t2.id                     , t1.last_date = t2.date 

it looks weird join table itself, doesn't know correct.

second working query following:

select * ip_table t1     date = (select max(date) ip_table id = t1.id) 

it afraid query can lead performance impact, because looks o(n^2), i.e. each row looks across rows.

you can use window function row_number:

select id, date, ip (    select id, date, ip,           row_number() on (partition id                               order date desc) rn          ip_table) t t.rn = 1 

row_number enumerates records within each id slice, starting record having latest date value (due order date desc clause). hence, outer query picks latest-per-id record.