title: Oracle SQL: Custom ORDER BY clause with a window aggregate function
body: <p>I'm working on putting together an Oracle SQL statement that can do some advanced sorting that I need.  So far here is my statement:</p>
  
  <pre><code>SELECT TIME, PLACE
  FROM TABLENAME
  ORDER BY MIN(year) OVER (PARTITION BY PLACE), PLACE, TIME;
  </code></pre>
  
  <p>I put this together after getting help with my other question (<a href="https://stackoverflow.com/questions/22922772/oracle-sql-grouping-ordering/22923259#22923259">Oracle SQL Grouping/Ordering</a>).  Now I need to throw seasons into the mix, however, they are not numbered numerically in the existing database.</p>
  
  <p>So let's say I have the following data</p>
  
  <pre><code>TIME   | PLACE
  198410 | somewhere
  198320 | work
  198431 | somewhere
  198232 | home
  198322 | work
  198211 | home
  198422 | somewhere
  </code></pre>
  
  <p>The first four digits of the TIME column are the year and the next digit is the season (where 2 = Summer, 3 = Fall, 1 = Winter), and the last digit should be ignored (for comparison reasons).</p>
  
  <p>The above query will produce the following result (groups the places together and orders them based on the time (numerically)):</p>
  
  <pre><code>TIME   | PLACE
  198410 | somewhere
  198422 | somewhere
  198431 | somewhere
  198320 | work
  198322 | work
  198211 | home
  198232 | home
  </code></pre>
  
  <p>However, I need a query that will take the weirdly coded seasons (Summer = 2, Fall = 3, Winter = 1) into account and produce the following chronologically sorted result:</p>
  
  <pre><code>TIME   | PLACE
  198422 | somewhere
  198431 | somewhere
  198410 | somewhere
  198320 | work
  198322 | work
  198232 | home
  198211 | home
  </code></pre>
  
  <p>I didn't make up this standard but unfortunately I have to work with it.  Any help on figuring this out is greatly appreciated.</p>