sql - How do I find the oldest date in Group -

i have table need oldest date group , able return rows. i'm finding difficult since need return system_id field.

 assignedprofshistory      matterid        effectivedate          1                 33434-3344       08/22/2005          2                 33434-3344       07/12/2004          3                 33434-3344       07/12/2004          4                 21122-323        12/05/2007          5                 43332-986        10/18/2014          6                 43332-986        03/23/2013  

so in example, rows systemid 2 & 3 should return because tied earliest date. row systemid 4 should return , systemid 6 should returned.

this have far. because need include systemid(assignedprofhistory) i'm not getting results need.

select  aph.assignedprofshistory,         m.matterid,         min(aph.effectivedate) 'effectivedate' assignedprofshistory aph inner join matters m on aph.matters = m.matters aph.assignedtype = 'originating' group m.matters,m.matterid,aph.assignedprofshistory order m.matterid 

any idea how results need?

thank in advance.

since want keep ties, i'd this:

select t2.assignedprofshistory, m.matterid, t2.effectivedate (    select matterid, min(effectivedate) med     assignedprofshistory     assignedtype = 'originating'    group matterid ) t1 inner join assignedprofshistory t2 on t2.matterid = t1.matterid     , t2.effectivedate = t1.med , t2.assignedtype = 'originating' inner join matters m on m.matters = t2.matters order m.matterid 

here sqlfiddle without matters table demonstrates can work, no windowing functions or cte required, though cte allow avoid repeating assignedtype='originating' condition.