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.
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'
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
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?