title: SQL Server 2008 how to select top [column value] and random record?
body: <p>I'm using SQL Server 2008, I want select random row record, and the total number of record is depend on another table's column value, how to do this?</p>
  
  <p>My SQL statement is something like this, but wrong..</p>
  
  <pre><code>select top b.number a.name, a.link_id 
  from A a 
  left join B b on b.link_id = a.link_id 
  order by newid()
  </code></pre>
  
  <p>Here are my tables and the expected result.</p>
  
  <p><strong>Table A</strong>:</p>
  
  <pre><code>name   link_id 
  james  100
  albert 100
  susan  100
  simon  101
  tom    101    
  fion   101
  </code></pre>
  
  <p><strong>Table B</strong>:</p>
  
  <pre><code>link_id   number
  100       2
  101       1
  </code></pre>
  
  <p>Expected result:</p>
  
  <ul>
  <li><p>when run 1st time, result may be:</p>
  
  <pre><code>name   link_id
  james  100
  susan  100
  fion   101
  </code></pre></li>
  <li><p>2nd time result may be:</p>
  
  <pre><code>albert 100
  susan  100
  simon  101
  </code></pre></li>
  <li><p>3rd time could be:</p>
  
  <pre><code>james  100
  albert 100
  fion   101
  </code></pre></li>
  </ul>
  
  <p>Explaination</p>
  
  <p>Refer to table B, link_id: 100, number: 2 </p>
  
  <p>meaning that Table A should select out 2 random record for <code>link_id = 100</code></p>
  
  <p>and need to select 1 random record for <code>link_id=101</code></p>