 <p>First, create examples for all these cases, and order them correctly by their intended priority:</p> <pre><code>status date -------------- past_due d1 past_due d2 open d1 open d2 paid d2 paid d1 future ? draft d2 draft d1 </code></pre> <p>Then, assign numbers so that <code>ORDER BY status2, date2</code> would work correctly. (If we assume that dates are numbers, <code>-d2</code> is smaller than <code>-d1</code>, i.e., negating them reverses the sort direction.)</p> <pre><code>status date status2 date2 ------------------------------ past_due d1 1 d1 past_due d2 1 d2 open d1 2 d1 open d2 2 d2 paid d2 3 -d2 paid d1 3 -d1 future ? 4 ? draft d2 5 -d2 draft d1 5 -d1 </code></pre> <p>Then use <a href="http://www.sqlite.org/lang_expr.html#case" rel="nofollow">CASE expressions</a> to map the status values to these numbers, and to modify the dates accordingly. (<a href="http://www.sqlite.org/lang_datefunc.html" rel="nofollow">julianday()</a> returns a number if the date values are in a supported format; the actual meaning of Julian days does not matter as long as they compare correctly to each other.)</p> <pre><code>SELECT customer_id, customer_version, startDate, status FROM invoices WHERE customer_id = 123 AND customer_version = '321' ORDER BY CASE status WHEN 'past_due' THEN 1 WHEN 'open' THEN 2 WHEN 'paid' THEN 3 WHEN 'future' THEN 4 WHEN 'draft' THEN 5 END, CASE WHEN status IN ('past_due', 'open', 'future') THEN julianday(startDate) -- earliest date first ELSE -julianday(startDate) -- latest date first END LIMIT 1 </code></pre> <p>The <code>LIMIT 1</code> then returns only the first of these sorted rows (if any has been found).</p>