How Can I Write a SQL Query That Will Sort the Results Based on a Condition?

Question

This question is based on this other question of mine, and uses all of the same basic info. That link shows my table layouts and the basic gist of the simple join.

I would like to write another query that would select EVERY record from Table1, and simply sort them by whether or not Value is less than the linked Threshold.

Again, I appreciate anyone who is willing to take a stab at this. Databases have never been a strong point of mine.

Answer 1

SELECT t1.LogEntryID, t1.Value, t1.ThresholdID, 
    case when t1.Value < t2.threshold then 1 else 0 end as Rank
FROM Table1 t1 
INNER JOIN Table2 t2 ON t1.ThresholdID = t2.ThresholdID 
ORDER By Rank

You can add DESC after ORDER By Rank if you want the reverse order.

Answer 2

Similar to the answer given by OrbMan, but I do prefer the CASE to be explicit in the ORDER BY, so that you are not forced to display your order by column.

SELECT
  t1.LogEntryID
 ,t1.Value
 ,t1.ThresholdID
FROM 
  Table1 t1
  JOIN Table2 t2 ON t2.ThresholdID = t1.ThresholdID
ORDER BY
  CASE WHEN t1.Value < t2.threshold
       THEN 1
       ELSE 0
  END ASC

Answer 3

Just a thought, but couldn't you use 'threshold - value' as one of your return columns?

If you did (and the columns were numeric) you would also be able to see how close (or far) from the threshold the value was.

anything positive or 0 would be under the threshold, anything negative wouldn't.