Sort by two feilds 1

[alexanderthegreat]

I am trying to sort by two feilds but it doesnt seem to work the way I want, I want it so sort by result ASC which is a calucation feild and Price DESC,

Also how do I edit the select statement so that when it selects results it changes any 0 value to a N/A, and ranks it higher in a sort Ascending so as not to show before a any of the numeric values?

Thanks
Al

SELECT result, BATHS_FULL, BATHS_PART, BDRMS, MLS_NUMBER, PRICE, SCHOOLS_D, SCHOOLS_E, SCHOOLS_H, SQFT, TAX, YEAR_BUILT, ZIP, TOTAL_APT_UNITS, STYLE, CITY, COUNTY, Expr1, Expr2
FROM dbo.v_mult
ORDER BY result ASC, PRICE DESC

 

[DotNetGnat]

something like this...

Select 
   (case when result=0 then 'N/A' else result end),
BATHS_FULL, BATHS_PART, BDRMS, MLS_NUMBER, PRICE, SCHOOLS_D, SCHOOLS_E, SCHOOLS_H, SQFT, TAX, YEAR_BUILT, ZIP, TOTAL_APT_UNITS, STYLE, CITY, COUNTY, Expr1, Expr2
FROM dbo.v_mult
ORDER BY (case when result=0 then 'AAAA' else result) ASC, PRICE DESC

-DNG

 

[r937]

if result is 0, you want those values last, correct?


ORDER BY
case when result=0 then 0 else 1 end
, result

r937.com | rudy.ca

 

[alexanderthegreat]

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.

Incorrect Syntax near ')'

Al

 

[r937]

oh, alex, would you mind showing the query that this error message was produced for?

r937.com | rudy.ca

 

[DotNetGnat]

from the query i provided remove the word ASC

ORDER BY (case when result=0 then 'AAAA' else result) ASC, PRICE DESC

-DNG

 

[r937]

dng, you fergot the "end"

and ASC, in the place you had it, is perfectly okay

and don't forget, if result is numeric, you may not be able to mix 'AAAA' and result in the sort column



r937.com | rudy.ca

 

[DotNetGnat]

oops...Thanks for the correction r937

-DNG

 

[alexanderthegreat]

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.