title: row_number based on case when with text content
body: <p>Here is a task I need to get three elements based on the given conditions:
  three elements: user_id, order_time, ordered_subject</p>
  <ol>
  <li>each unique user_id</li>
  <li>earliest order_time</li>
  <li>ordered_subjects' order should be app-&gt; acc -&gt;ayy</li>
  <li>if there are several order_time are the same, you should take only one subject followed by the 3rd requirement</li>
  </ol>
  <p>original table: user_order</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>user_id</th>
  <th>order_time</th>
  <th>ordered_subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2001-02-09</td>
  <td>app</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2001-02-09</td>
  <td>app</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2001-02-10</td>
  <td>ayy</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2001-02-09</td>
  <td>acc</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2001-02-10</td>
  <td>app</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2001-02-08</td>
  <td>ayy</td>
  </tr>
  <tr>
  <td>5</td>
  <td>2001-02-09</td>
  <td>acc</td>
  </tr>
  <tr>
  <td>5</td>
  <td>2001-02-09</td>
  <td>ayy</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>expected table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>user_id</th>
  <th>order_time</th>
  <th>ordered_subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2001-02-09</td>
  <td>app</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2001-02-09</td>
  <td>app</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2001-02-10</td>
  <td>ayy</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2001-02-08</td>
  <td>ayy</td>
  </tr>
  <tr>
  <td>5</td>
  <td>2001-02-09</td>
  <td>acc</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I come up with the idea of <strong>case when</strong> and <strong>row_number() over</strong>, but it doesn't work</p>
  <p>the code I tried:</p>
  <pre><code>select 
  a.uid, 
  a.subject, 
  b.min_time,
     (case when &quot;app&quot; then 1 
             when &quot;acc&quot; then 2 
             when &quot;ayy&quot; then 3 
             else 4 end) as rn, 
     row_number() over(partition by 
                         concat(uid,order_id) 
                         order by 
                         rn)
  from (
     select uid, min(order_time) as min_time
     from user_order
     group by
     uid
     ) as b
  -- join
  user_order as a
  -- on
  where
  a.uid = b.uid
  and
  b.min_time = a.order_time 
  </code></pre>
  <p>How should I fix this?</p>