title: Select value from table sorted by a certain order from another table
body: <p>I want to select value from table sorted by a certain order.</p>
  
  <p>I have a table called test that looks like this:</p>
  
  <pre><code>| date     | code      | value    |
  +----------+-----------+----------+
  | 20050104 | 000005.SZ | -6359.19 |
  | 20050104 | 600601.SH | -7876.34 |
  | 20050104 | 600602.SH | -25693.3 |
  | 20050104 | 600651.SH |   NULL   |
  | 20050104 | 600652.SH | -15309.9 |
  ...
  | 20050105 | 000005.SZ | -4276.28 |
  | 20050105 | 600601.SH | -3214.56 |
  ...
  | 20170405 | 000005.SZ | 23978.13 |
  | 20170405 | 600601.SH | 32212.54 |
  </code></pre>
  
  <p>Right now I want to select only one date, say date = 20050104, and then sort the data by a certain order (the order that each stock was listed in the stock market).</p>
  
  <p>I have another table called stock_code which stores the correct order:</p>
  
  <pre><code>+---------+-----------+
  | code_id | code      |
  +---------+-----------+
  |       1 | 000002.SZ |
  |       2 | 000004.SZ |
  |       3 | 600656.SH |
  |       4 | 600651.SH |
  |       5 | 600652.SH |
  |       6 | 600653.SH |
  |       7 | 600654.SH |
  |       8 | 600602.SH |
  |       9 | 600601.SH |
  |      10 | 000005.SZ |
  ...
  </code></pre>
  
  <p>I want to sorted the selected data by stock_code(code_id), but I don't want to use join because it takes too much time. Any thoughts?</p>
  
  <p>I tried to use field but it gives me an error, please tell me how to correct it or give me an even better idea.</p>
  
  <pre><code>select * from test 
      where date = 20050104 and code in (select code from stock_code order by code)
      order by field(code, (select code from stock_code order by code));
  
  Error Code: 1242. Subquery returns more than 1 row
  </code></pre>