Hi Dave,
     Below, you can see what my query has in the select statement now. But I get an error message that says: âAn expression of non-boolean type specified in a context where a condition is expected, near 'THEN'. â You should also know that there is an order by and group by in the subquery, as well as the order by of the main query. Is it possible I have parenthisis in the wrong place or too many parenthisis or not enough?  SELECT * FROM (                                    SELECT (CASE WHEN isNumeric(a.ClCo) THEN            CONVERT(VARCHAR(50), CONVERT(NUMERIC(8), a.ClCo) )            ELSE a.ClCo END ) AS AccountSort,            a.Name, a.SRep, a.RGrp, a.DtAq,                                    <CFIF breakDownByOrigin eq "Y">                                                o.Orig,                                    </CFIF>                                    COUNT(DISTINCT o.JobN) AS jTotal,                                    SUM(o.ToTl - ISNULL(o.ESC1, 0) - o.tax1a) AS tTotal,                                    SUM(ISNULL(o.ESC1, 0)) AS eTotal,                                    SUM(o.ToTl - o.tax1a) AS rTotal,                                    SUM(ISNULL(d.dTotal,0)) AS cTotal                                    FROM ⦠  ) ORDER BY AccountSort -----Original Message----- From: Dave Phillips <[EMAIL PROTECTED]> To: cf-newbie <[email protected]> Sent: Tue, 28 Oct 2008 5:43 pm Subject: RE: Numerical order of query results Lewis, I just thought of a few things. First, try this: ORDER BY REPLICATE( '0', 10 - LEN(a.Clco))+ a.Clco Replace 10 with a number that would be greater than your LONGEST string in ..Clco. This way, all your values will have leading zeroes and should sort ppropriately. If that doesn't work, Try this: ELECT * FROM ( SELECT ....your columns here, and add this to your select list: (CASE WHEN isNumeric(a.ClCo) THEN onvert(varchar(50),convert(numeric(8), a.ClCo)) ELSE a.ClCo END) as y_string (or whatever you want to call it) ROM ... HERE .... ) ORDER BY my_string kay, so here's what this does....The SELECT query inside the parenthesis ill be your regular query along with this extra column. The case statement ill look at a.ClCo and if it is numeric, then it will convert it to numeric which will get rid of leading zeroes) and then convert it back to varchar which will make it a string). If it's NOT numeric (01DEMO), it will just rovide that. So you either get a string of an account number without eading zeroes or the actual account number, if it's a string, in your new olumn (I called it my_string). Now, all of this has to be in your regular uery, and you need to wrap that entire query with SELECT * FROM ( ...your query here... ) ORDER BY my_string This will allow you to order by that new column you created which should ork because all values will now be a string. Now you should get the 01DEMO t the top since it has a leading zero, but everything else will NOT have he leading zero, so they should sort normally. Dave ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4108 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
