title: Sorting for each group based if it has a value
body: <p>I have 2 tables. first is company table which contains the company ranking and the second table is storage units which indicates which storage belong to which company, ranking of each storage unit, and capacity of each storage unit. during processing, I only need the highest ranking storage unit belonging to the highest rated companies. take for example below:</p>
  <p><a href="https://i.stack.imgur.com/pINhx.png" rel="nofollow noreferrer">company</a></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CompanyID</th>
  <th>Company</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>storage units</p>
  <p><a href="https://i.stack.imgur.com/vnZ7S.png" rel="nofollow noreferrer">storageunit</a></p>
  <p>expected result:</p>
  <p><a href="https://i.stack.imgur.com/gLKWa.png" rel="nofollow noreferrer">expected1</a></p>
  <p>when storage units hve been updated:</p>
  <p><a href="https://i.stack.imgur.com/qDn5E.png" rel="nofollow noreferrer">storageunit2</a></p>
  <p>then result is as follows:</p>
  <p><a href="https://i.stack.imgur.com/26MfO.png" rel="nofollow noreferrer">expected2</a></p>
  <p>the current sorting being done is this:
  INNER JOIN storage_units AS s
  ON s.companyID = p.id
  WINDOW w AS (ORDER BY s.rank, p.rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  it works the first time but on second run if having this storage units table:
  <a href="https://i.stack.imgur.com/qDn5E.png" rel="nofollow noreferrer">storageunit2</a></p>
  <p>, it yields this result:
  <a href="https://i.stack.imgur.com/4C5Kb.png" rel="nofollow noreferrer">currentres</a></p>
  <p>Thank you for the help!</p>