Order by in the rollup

Ok, I want to know if it is possible to do an order by while using the rollup in the group by. Look at the below script:

create table tmpTable
(
prod_name varchar(50) null,
prod_color varchar(50) null,
quantity int null
)

insert into tmpTable values ('table', 'blue', 12)
insert into tmpTable values ('table', 'red', 100)
insert into tmpTable values ('table', 'white', 50)
insert into tmpTable values ('chair', 'blue', 12)
insert into tmpTable values ('chair', 'red', 1)
insert into tmpTable values ('chair', 'white', 123)
insert into tmpTable values ('chair', 'yellow', 50)

SELECT CASE WHEN (GROUPING(prod_name) = 1) THEN 'Grand Total'
ELSE ISNULL(prod_name, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(prod_color) = 1) and grouping(prod_name) != 1 THEN 'Sub Total'
when grouping(prod_color) = 1 and grouping(prod_name) = 1 then ''
ELSE ISNULL(prod_color, 'UNKNOWN')
END AS Color,
SUM(quantity) AS QtySum
FROM tmpTable
GROUP BY prod_name, prod_color WITH ROLLUP

--drop table tmpTable

I want to be able to do an order by for each section of the rollup so that the quantity can be asc for both the chair part of the query and the table part.

Thanks ahead of time.

DMWYou can try to use ORDER BY 1, 2 or something like that, but I am not sure you'll get what you expect.|||No, sadly its not. I've been playing around with it and I've come to determine that the rollup puts in the extra records in the rs and then sql server does the order by. I'll just have to live with what I have. Thanks anyway.