title: PostgreSQL: Sorting on alphanumeric string and decimal field
body: <p>Hope you all are fine and doing good!</p>
  
  <p>I have a table <code>items</code> with 2 million+ records the structure looks like below:</p>
  
  <pre><code>id (int) | price (decimal) | priority (int)
  -------------------------------------------
  10001    |  59000.25       |    1
  10002    |  73000.91       |    2
  10003    |  1000.23        |    1
  10004    |  9567.18        |    1
  </code></pre>
  
  <p>The solution that I am looking for is very straightforward: How do I sort this table on <code>price+priority</code> on ASC|DESC order?</p>
  
  <p><strong>Current and working solution</strong>: I am using <code>ORDER BY priority ASC, price ASC</code>. But as far as my knowledge goes, sorting on multiple column is slow and not optimised approach (and I am facing realtime slowness because of this).</p>
  
  <p><strong>Solutions I tried</strong>: I've added a temporary column to this table:</p>
  
  <pre><code>id (int) | price (decimal) | priority (int) | new_priority (varchar)
  --------------------------------------------------------------------
  10001    |  59000.25       |    1           | a59000.25
  10002    |  73000.91       |    2           | b73000.91
  10003    |  1000.23        |    1           | a1000.23
  10004    |  9567.18        |    1           | a9567.18
  </code></pre>
  
  <p>I've replaced <code>1 =&gt; a, 2 =&gt; b, 3 =&gt; c</code> up till 10 (max number I have in database)</p>
  
  <p>Now, whenever I am trying below SQLs, none of them is working</p>
  
  <pre><code>SELECT * FROM items
  ORDER BY new_priority ASC
  
  SELECT * FROM items
  ORDER BY new_priority::bytea
  
  SELECT * FROM items
  ORDER BY SUBSTRING(new_priority FROM '^(.*?)( \\d+)?$'),
       COALESCE(SUBSTRING(new_priority FROM ' (\\d+)$')::INTEGER, 0)
  </code></pre>
  
  <p>Please advise!</p>
  
  <p>Links I referred:</p>
  
  <ol>
  <li><a href="https://stackoverflow.com/questions/7018628/postgresql-sorting-mixed-alphanumeric-data">Postgresql sorting mixed alphanumeric data</a></li>
  <li><a href="https://stackoverflow.com/questions/43175737/alphanumeric-sorting-in-postgresql">Alphanumeric Sorting in PostgreSQL</a></li>
  <li><a href="https://stackoverflow.com/questions/11417975/alphanumeric-sorting-with-postgresql">Alphanumeric sorting with PostgreSQL</a></li>
  <li><a href="https://stackoverflow.com/questions/15427529/alphanumeric-case-in-sensitive-sorting-in-postgres">Alphanumeric case in-sensitive sorting in postgres</a></li>
  </ol>