Formatted number column cannot be sorted by number

[dlite922]

This is again from the dynamic reporting module i'm building.

Columns come in and are built based on XML doc and the HTML (smarty template) does not know weather this is a number to format it nicely such as 2345.993 to 2,346.99.

So I do my formatting in the XML which gives the dynamic query developer something similar this:

FORMAT(price * qty,2) AS sales

All was fine and dandy until i wanted to sort it by number. Turns out Format makes it as string and it does a string sort, in other words it would sort it like this:

1223
21
90

So a genius (/sarcasm) told me to "add a zero" to it and it would work.

nope, it turned 2,345.00 into 2.00. (had to explain to the client why his biggest sale was showing up as 2.00)

Now i've converted it to ROUND()

but is there any other way to have the comma (thousands delimiter) as well has have mySQL treat it as a number?

Thanks for your feedback,


Dan

[r035198x]

I don't think formatting should be done by the database at all.
The front end should format based on the user's preferences. What are you using for the front-end?

[Atli]

I would have to agree with r035198x.

Any manipulation of the data should be done at a "presentation level"... i.e. using whatever API you use to present the data to you users.

[dlite922]

I would have to agree with r035198x.

Any manipulation of the data should be done at a "presentation level"... i.e. using whatever API you use to present the data to you users.

guys, guys, read the OP. I said i'm using smarty, but since its all dynamic, how could smarty know weather its a text, a phone number or a dollar amount.

If there is no way, then I'll live with it and switch to ROUND(). That's all I was asking. Maybe I wasn't clear. Sometimes I babble on and on..like this. :)

thanks for the replies though,


Dan