Custom Result Order using UNION - TSQL

I have a fairly simple task in which I need to select rows with a particular field with a value of 1 first, then a value of 2 and finally all other rows with a value of 0.

I using a join to get which items are a member of which category. I thought that performing 3 selects and using a union would combine to results in the correct order, but I was wrong :)

My SQL is开发者_高级运维 as follows:

SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 1 
UNION 
SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 2
UNION 
SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 0

My results do not come out ordered with 1, then 2 and then 0.. What am I doing wrong here??

* SOLUTION * Thanks for the help guys. Below is the final SQL I am using.

SELECT * FROM tblcompanycategory 
    INNER JOIN tblcompany
    ON tblcompany.idcompany = tblcompanycategory.idcompany
    WHERE tblcompanycategory.idcategory = @category 
    ORDER BY CASE tblcompany.intpremium WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 0 THEN 2 END

---

The UNION statement, like all good set operators, is agnostic when it comes to ordering data.

What you can do, however, is run one select statement with a carefully-crafted ORDER BY statement, such as:

... ORDER BY CASE tblCompany.IntPremium WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 0 THEN 2 END

---

Ordering does not occur by the order of your select statements in a union clause. The simplest thing to do is add an order by:

SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 1 
UNION 
SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 2
UNION 
SELECT * FROM tblcompanycategory 
INNER JOIN tblcompany
ON tblcompany.idcompany = tblcompanycategory.idcompany
WHERE tblcompanycategory.idcategory = @category AND tblcompany.intpremium = 0

ORDER BY CASE intpremium WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 0 THEN 3 END

---

You'll need to add a column to the three select statements, with a literal value that "categorizes" the results. Then order by on that column.