title: Calculating percent rank in MySQL
body: <p>I have a table of data that contains costs. For any given record, I would like to know where the record falls in relation to other costs.</p>
  
  <p>Basically, I want to know (how many records have lower costs) / (total number of records).</p>
  
  <p>So far, I've come up with:</p>
  
  <pre><code>SELECT (SUM(IF(r.cost &gt; c.cost, 1, 0)) + 1) / COUNT(1) as percent_rank 
  FROM record r, 
     (SELECT cost FROM record WHERE cost IS NOT NULL) as c 
  WHERE r.id = 10;
  </code></pre>
  
  <p>Running EXPLAIN on this query produces:</p>
  
  <pre><code>+----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
  | id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
  +----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
  |  1 | PRIMARY     | r          | const | PRIMARY       | PRIMARY | 4       | const |     1 |             |
  |  1 | PRIMARY     | &lt;derived2&gt; | ALL   | NULL          | NULL    | NULL    | NULL  | 21469 |             |
  |  2 | DERIVED     | record     | ALL   | NULL          | NULL    | NULL    | NULL  | 21469 | Using where |
  +----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
  </code></pre>
  
  <p>Is there a better way of running this query? 21469 is all the records in the table. I'm a bit worried about the cross join but this query would always be run with id = something so it's just 1 * total num rows.</p>