title: SQL Joining table with Min and Sec Min row
body: <p>I want to join  table 1 with table2 twice becuase I need to get the first minimum record and the second minimum. However, I can only think of using a cte to get the second minimum record. Is there a better way to do it?</p>
  
  <p>Here is the table table:</p>
  
  <p>I want to join Member with output table FirstRunID whose Output value is 1  and  second RunID whose Output value is 0</p>
  
  <p><a href="https://i.stack.imgur.com/cy6Nt.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cy6Nt.jpg" alt="enter image description here"></a></p>
  
  <p>current code I am using:</p>
  
  <pre><code>select memid, a.runid as aRunid,b.runid as bRunid 
  into #temp
  from FirstTable m inner join
  (select min(RunID), MemID [SecondTable] where ouput=1 group by memid)a on m.memid=a.memid
  inner join (select RunID, MemID [SecondTable] where ouput=0 )b on m.memid=a.memid and b.runid&gt;a.runid
  
  with cte as
  (
  select row_number() over(partition by memid, arunid order by brunid ),* from #temp
  )
  
  select * from cte where n=1
  </code></pre>