You'll need to put the same IF statement into the order by clause. SQL will only sort on columns that actually exist in the table, not dynamically calculated columns (such as your orderprice alias).
SELECT SQL_CALC_FOUND_ROWS P.normalprice, P.saleprice, IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) as orderprice FROM product P <cfif len(session.pricerange)> AND orderprice >= <cfqueryparam cfsqltype="cf_sql_integer" value="#listfirst(session.pricerange)#"> AND orderprice <= <cfqueryparam cfsqltype="cf_sql_integer" value="#listlast(session.pricerange)#"> </cfif> ORDER BY IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) HTH, Carl > extra for experts... > > though the following would work, but gives error... > > SELECT SQL_CALC_FOUND_ROWS P.normalprice, P.saleprice, > IF(P.saleprice < P.normalprice, P.saleprice, P.normalprice) as > orderprice > FROM product P > <cfif len(session.pricerange)> > AND orderprice >= <cfqueryparam cfsqltype="cf_sql_integer" > value="#listfirst(session.pricerange)#"> > AND orderprice <= <cfqueryparam cfsqltype="cf_sql_integer" > value="#listlast(session.pricerange)#"> > </cfif> > ORDER BY orderprice > > if i replace orderprice with "P.normalprice >= ..." it works. however > i want it to see which is the lowest first. > > mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335129 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
