CODEWITHSUNDEEP
sql-serverselect
user1345260
user1345260

Reputation: 2259

How to combine result of two SELECT Statements

I'm a newbie for SQL Server. Please help me out with my below problem.

I've a table as below,

Job  Quantity Status
1      100      OK
2      400      HOLD
3      200      HOLD
4      450      OK

I would like to write a query now in such a way that all the Jobs with Quantity equal to or more than 400 with status OK would appear first then Jobs with Quanitty equal to or more than 400 with status HOLD. Then all the Jobs with Quantity less than 400 with Status OK would appear and after that Jobs with quantity less than 400 with status HOLD should appear.

Here's how my result should appear. (Sorry for the above confusing paragraph)

Job  Quantity Status
4      450      OK
2      400      HOLD
1      100      OK
3      200      HOLD

How do I do this? Some one please help me out

Upvotes: 3

Views: 490

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Here are some slightly less verbose alternatives:

ORDER BY Quantity / 400 DESC,
  CASE [Status] WHEN 'OK' THEN 1 ELSE 2 END;

This treats jobs with quantity >= 800 as higher priority. A better alternative is probably:

ORDER BY CASE Quantity / 400 WHEN 0 THEN 2 ELSE 1 END,
  CASE [Status] WHEN 'OK' THEN 1 ELSE 2 END;

Also if OK and HOLD are the only two possibilities for [Status], you can just say:

ORDER BY CASE Quantity / 400 WHEN 0 THEN 2 ELSE 1 END,
  [Status] DESC;

Upvotes: 1

erikkallen
erikkallen

Reputation: 34421

SELECT Job, Quantity, Status 
  FROM myTable
 ORDER BY CASE WHEN Quantity >= 400 AND Status = 'OK' THEN 1 
               WHEN Quantity >= 400 AND Status = 'Hold' THEN 2
               WHEN Status = 'OK' THEN 3
               ELSE 4
          END

Upvotes: 6

Related Questions