SQLTeam.com | Weblogs | Forums

Select first top row


#1

HI,

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


#2

One of these will probably do

Query 1
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
;

#3

Thanks Bitsmed, both Queries works the same way. looks good! Just had to change the inner join to LEFT join to get the correct count.
Pasi