Order by two columns as if they where one.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order by two columns as if they where one.

Author

Topic

curry
Starting Member

14 Posts

Posted - 2008-07-19 : 11:33:16

Hi

I am trying to order a recordset of products by price. Each product has a price in a column named "Price" but some also have a price in a column named "SalePrice". The order has to be based on the normal "Price" unless a "SalePrice" exists.

Example Data.

tbl_products As in DB:
ID|Name|Price|SalePrice
1|Prod1|£10.00|£0.00
2|Prod2|£20.00|£5.00
3|Prod3|£30.00|£25.00
4|Prod4|£40.00|£0.00
5|Prod5|£50.00|£35.00

Needs to be ordered as:
ID|Name|Price|SalePrice
2|Prod2|£20.00|£5.00
1|Prod1|£10.00|£0.00
3|Prod3|£30.00|£25.00
5|Prod5|£50.00|£35.00
4|Prod4|£40.00|£0.00

I've managed to get it to work fine like this

SELECT products.productid, products.Price,
ProdPrice = CASE products.saleprice 
when 0 THEN products.price
else products.saleprice
end, products.saleprice
FROM products
where catcode=61
order by prodprice desc

But when I use it in a sproc and with a case expression for ORDER BY is fails with an error stating that ProdPrice is an invalid column name.

order by
CASE WHEN @orderb = '0' THEN productid
END desc,
CASE WHEN @orderb = '1' THEN productid
END asc,
CASE WHEN @orderb = '2' THEN ProdPrice
END asc,
CASE WHEN @orderb = '3' THEN name
END asc,
CASE WHEN @orderb = '4' THEN ProdPrice
END desc,
CASE WHEN @orderb = '5' THEN name
END desc

It returns an error stating that ProdPrice is an invalid column. It works fine with Price or SalePrice so I can only guess it has something to do with the fact that the ProdPrice column doesn't exist in the DB and is created in the SQL statement/sproc.

I really am stuck on this one.

Thanks in advance

Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 12:26:14

you dont require the CASE WHEN in select list just use like this

SELECT products.productid,Name,Price,SalePrice
FROM products
where catcode=61
order by coalesce(nullif(SalePrice,0.00),Price) desc

and if you want to use CASE WHEN in order by use like this

order by
CASE WHEN @orderb = '0' THEN productid
END desc,
CASE WHEN @orderb = '1' THEN productid
END asc,
CASE WHEN @orderb = '2' THEN coalesce(nullif(SalePrice,0.00),Price)
END asc,
CASE WHEN @orderb = '3' THEN name
END asc,
CASE WHEN @orderb = '4' THEN coalesce(nullif(SalePrice,0.00),Price)
END desc,
CASE WHEN @orderb = '5' THEN name
END desc

curry
Starting Member

14 Posts

Posted - 2008-07-21 : 05:13:20

Thank you visakh16

That works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources