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.
Author |
Topic |
coopere905
Starting Member
1 Post |
Posted - 2012-10-22 : 15:38:22
|
I have two case statements in by select clause. The first case (values) places the data into four categories of price range while the second (listed as) separates those ranges into 'none' and 'co-listed'.Essentially you get: VALUES__LISTED AS[PRICE2] [Co-Listed][PRICE2] [None][PRICE3] [Co-Listed][PRICE3] [None][PRICE1] [Co-Listed][PRICE1] [None]In the script I've put the case statements in the order, 'values' first. What happens is, as above, the prices are not being ordered in the way that I wrote them. The [PRICE3]'s should be at the bottom while [PRICE1]'s, the top. Any suggestions? I've tried changing the order in which the case statements are written in the query, but that doesn't solve the problem.The code is somewhat lengthy, but I can post it if need be. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-22 : 15:46:01
|
This happens because the Values column is not numeric - it is a string. In string sort, each character in a given row starting from the left is compared to the corresponding character in another row. 1 is lower than 3 even though 1000001 is greater than 300000. Hence the behavior that you see.If you have another column that is numeric - for example, one that shows the starting values (to the left of the "to"), order it by that column. |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2012-10-22 : 18:03:17
|
coopere905,please post some example of your code. and please provide data types as well. PRICE2 = Integer? Co-Listed =Varchar?thanks |
|
|
|
|
|