working on a query to only give me the first instructor only from the top, in this case I have 4 different instructor for 2013-02-21 and same for 2013-01-10, I only one the first instructor for each date?
Thanks!
Pasi
select
,sf.facilitatr
from SESSION s
join training pt on pt.LEARN_ID =s.LEARN_ID and s.session=pt.session_id
join SE_FACILITA SF on SF.SESSION_ID = s.SESSION and pt.LEARN_ID =s.LEARN_ID
select top(1) with ties
pt.learn_id as learnID
,pt.session_id
,pt.session_start_time as STR_TM
,pt.session_start_dt
,pt.session_end_dt
,pt.duration_type
,sf.facilitatr
from session as s
inner join training as pt
on pt.learn_id=s.learn_id
and pt.session_id=s.session
inner join se_facilitatr as sf
on sf.session_id=s.session
and pt.learn_id=s.learn_id
order by row_number() over(partition by pt.learn_id
,pt.session_id
,pt.session_start_time
,pt.session_start_dt
,pt.session_end_dt
,pt.duration_type
order by sf.facilitatr
)
;
Query 2
select pt.learn_id as learnID
,pt.session_id
,pt.session_start_time as STR_TM
,pt.session_start_dt
,pt.session_end_dt
,pt.duration_type
,min(sf.facilitatr) as facilitatr
from session as s
inner join training as pt
on pt.learn_id=s.learn_id
and pt.session_id=s.session
inner join se_facilitatr as sf
on sf.session_id=s.session
and pt.learn_id=s.learn_id
group by pt.learn_id
,pt.session_id
,pt.session_start_time
,pt.session_start_dt
,pt.session_end_dt
,pt.duration_type
;