title: SQL CASE: Does the order of the WHEN statements matter?
body: <p>I am using PHP to generate a SQL query that needs to be ordered in a custom way. I am generating a <code>CASE</code> block with a number of <code>WHEN</code> statements that assign a number ranking. The <code>WHEN</code> statements that are included are contingent on how much information I have available for querying. For example, if I have a phone available, I will generate three <code>WHEN</code> statements:</p>
  
  <p><code>WHEN phone = '(202) 555-5555' AND last_name = 'Smith' and first_name = 'John' THEN 1</code></p>
  
  <p><code>WHEN phone = '(202) 555-5555' AND last_name = 'Smith' THEN 2</code></p>
  
  <p><code>WHEN phone = '(202) 555-5555' THEN 3</code></p>
  
  <p>So I am ranking based on how close the match is, and I am valuing the parameters the same (a match on phone, first name, and last name should be ranked on the same tier as a match on address, first name and last name). However, after generating these statements for multiple pieces of information (phone, address, etc), my <code>WHEN</code> statements will be all out of order; the <code>THEN 1</code> terminated clauses will be separated from each other, same for <code>THEN 2</code> and so on. I could put them in order, but that would make my PHP more verbose and ugly.</p>
  
  <p><strong>TL;DR?</strong></p>
  
  <p>So the <strong>short question</strong> is: Does the order of the <code>WHEN</code> statements in a <code>CASE</code> statement matter? Does SQL (I'm using Oracle) go with the first match or does it evaluate all of the possibilities and assign the highest ranking?</p>