DB2 - handling DISTINCT

[amishraa]

Hello,

What seems so trivial, has been bugging me. I have a table with duplicate values on one column, while non-duplicate (date) values on the other. If I am to use DISTINCT it will only eliminate the row with the same date. But how do I use DISTINCT and only keep the ones with the oldest date?

I hope this does not come out as a dumb question. I have tried reviewing other posts to see if I could find my answer there but had no luck.

Any help would be greatly appreciated!

Example:

Student ModifiedDate
John    06222015
John    06212015

What I would like is:

Student ModifiedDate
John    06212015

[amishraa]

select distinct school_nm,STUDENT_ID,"Return Dt","Detention Dt","Days on Detention" from (
select school.school_nm,event.STUDENT_ID,event.log_dt "Return Dt", hold_orders.CREATE_DT_TM "Detention Dt" , days(event.log_dt) - days(hold_orders.CREATE_DT_TM) "Days on BO"
from (select * from dbo.school
where school_nm='BURKE HIGH'
) school
inner join (select * from dbo.log_dt where evt_cd='DETN' 
and log_dt > CURRENT TIMESTAMP - 8 DAY and log_dt < CURRENT TIMESTAMP - 1 DAY
) event
on school.school_id=event.school_id
left outer join (select * from dbo.hold_orders 
where hold_reason_cd ='ATTENDANCE%') hold_orders
on hold_orders.STUDENT_ID  = event.STUDENT_ID
order by event.log_dt
)
where "Days on BO">=0

school_nm STUDENT_ID Return Dt Detention Dt Days on Detention
BURKE HIGH 135435 6/18/2015 6/18/2015 0
BURKE HIGH 103242 6/22/2015 6/20/2015 2
BURKE HIGH 103242 6/21/2015 6/20/2015 1
BURKE HIGH 103242 6/23/2015 6/20/2015 3

How do I only keep the first, and the third record?

[amishraa]

Well, nevermind then.

[diafol]

Is there a reason why you're storing the date in this format? It can't be sorted easily YYYYMMDD would allow easy sorting