title: Finding The top ranked match in stored procedure
body: <p>I have a situation for which I need guidance/solution.</p>
  <p>I have a table which contains the Order as below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>color</th>
  <th>rank</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Red</td>
  <td>1</td>
  </tr>
  <tr>
  <td>Blue</td>
  <td>2</td>
  </tr>
  <tr>
  <td>Green</td>
  <td>3</td>
  </tr>
  <tr>
  <td>Yellow</td>
  <td>4</td>
  </tr>
  <tr>
  <td>White</td>
  <td>5</td>
  </tr>
  <tr>
  <td>Black</td>
  <td>6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have another table like below</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>ColorValue</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ID1</td>
  <td>Red,Yellow</td>
  </tr>
  <tr>
  <td>ID2</td>
  <td>Green</td>
  </tr>
  <tr>
  <td>ID3</td>
  <td>White</td>
  </tr>
  <tr>
  <td>ID4</td>
  <td>Green,Red</td>
  </tr>
  <tr>
  <td>ID5</td>
  <td>Yellow, White,Black</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Now, the first table defines the selected color based on priority and would like colorValue to be replaced with the color which is ranked highest (1 is high and 6 is lowest).</p>
  <p>The output I want is as below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>ColorValue</th>
  <th>Value to be replaced</th>
  <th></th>
  <th>reason</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ID1</td>
  <td>Red,Yellow</td>
  <td>Red</td>
  <td></td>
  <td>Red is ranked higher than yellow</td>
  </tr>
  <tr>
  <td>ID2</td>
  <td>Green</td>
  <td>Green</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>ID3</td>
  <td>White</td>
  <td>White</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>ID4</td>
  <td>Green,Red</td>
  <td>Red</td>
  <td></td>
  <td>Red is ranked higher than Green</td>
  </tr>
  <tr>
  <td>ID5</td>
  <td>Yellow, White,Black</td>
  <td>Yellow</td>
  <td></td>
  <td>Yellow is ranked highest among 3 colors</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Please let me know how can I achieve this.</p>