Union All and Multiple Order By's how?

[efontana]

Ok; I have a table with a list of products, for example

ProductID Name Price
1 Red 2
2 Blue 1
3 Green 3
4 Orange 3
5 White 4
6 Blue 5

I want to give people the option of "featuring" products, and also to be able to control the order of featured products by price

So, I started with this:

// Featured
select *, 1 as Ordinal from Products where ProductID in (1,4)
union all
select *, 2 as Ordinal from Products where ProductID not in (1,4)

This query works fine, the order returned is:

1,4,2,3,5,6

What I want to do is this:
select *, 1 as Ordinal from Products where ProductID in (1,4) order by Price
union all
select *, 2 as Ordinal from Products where ProductID not in (1,4)

But, adding the order by price cause an error, I realize the order by
should be last, but I need it on the "first set", and so on,

Any ideas? stored procedure? views?

[sknake]

The order by should appear in the last query of a union but it applies to ALL data in the union. You will be ordering all of the queries by the same column. If you want to have the union'd queries ordered by the query order then you could:

select *, 1 as Ordinal from Products where ProductID in (1,4) 
union all
select *, 2 as Ordinal from Products where ProductID not in (1,4) Order By Ordinal, Price

Please use code tags in the future when you post.