MySQL Order by a calculation

I have a table which contains values in different currencies. I was wondering if it is possible to order by the price after converting the prices into 1 currency.


For example;

Table has fields "id", "product name", "currency", "price"
With records;

"1","Product A","$","150"
"2","Product B","$","100"
"3","Product C","€","100"

Today €1 = $1.32 which means that Product 3 is $132

The desired result for sorting the list would be;

"2","Product B","$","100" ($100)
"3","Product C","€","100" ($132)
"1","Product A","$","150" ($150)


I realise I could create another column and create the total in dollars every time I go to do the sorting but I wondered if there was a way to perform this operation in 1 query?

 

Hi,

You don't need another column, just simulate it using control flow functions
Regards

 

You can order by the calculated price. I can't give you a specific example because I don't know where/how you have your currency conversion factors stored.

 

Try it like this:

SELECT column1, column2*column3 AS result ORDER BY result

or

SELECT column1, column2*132 AS result ORDER BY result

 

I think I need to try and use a combination of your suggestions as I can't do calculations until I know if the price column needs converting, based on the column containing the currency.

Thanks for your help

 

If your currency table contains a value of '1' for $-$, EUR-EUR, etc., then you can just go ahead and multiply by your conversion factor without worrying about those cases.

 

Cna you explain this a bit further please?

 

Well, I assume you have some tables like:

table currency
currency_id int auto_increment not null
symbol varchar(5)
name varchar(255)
isocode char(3)

table currency_rate
currency1_id int not null
currency2_id int not null
rate float

So then just make sure you have an entry in currency_rate where currency1_id = currency2_id and rate = 1.

 

The ideal thing to do performance-wise would be to create another column and have a cron-job/automated-task calculate the universal price for that column periodicly.

If you must have to-the-second accuracy however, you can calculate that universal price column on the fly with every single request using CASE.

Assuming you have a table using the fields you have in your first post, the "currecny" field is a CHAR(1) using a UTF8_BIN collation, and you have a UTF8 connection established to the MySQL server, something like this would return rows in lowest-price-first order.

SELECT `id` , `product_name` , `currency` , `price` , 
	CASE WHEN `currency` = '€' THEN (`price` * 1.13)
	ELSE `price` END AS `universal_price` 
FROM `products` 
ORDER BY `universal_price`
ASC

Code (markup):

 

Sorry, but that's positively nutty. So slow to run and not at all scalable. Just stick your conversions in another table as I sketched above and then you can calculate on the fly to/from any number of currencies.

 

Currency must be ENUM. In ENUM column data is representing as string and as number, because it is stored internally as number, so no future database denormalisation is required.

Using CASE ( or other comparison ) will not slow down the query. Same comparison ( per each result row ) there is in simple query like:

SELECT * FROM TABLENAME WHERE COLUMN=1;

Code (markup):

Using ENUM there is no need to use BIN collation nor to change connection named pipe.

Regards: Nick

 

Prove it.

 

Prove that it's slow or that it's not scalable?

That it's not scalable should be self-evident; what happens when you want to add a new currency? You have to track down every select and extend the set of cases. What happens when you grow and have to deal with 50 currencies? You have a query that takes real time to parse.

As the application begins to take on real-world use-case characteristics, the flaws of trying to jam everything into a clump of case clauses grows. Currencies have different forms of expression for different contexts (ISO code, symbol, English name, local name, etc.). Cross rates are not always symmetrical. Are you going to stick all that information in every query? Might as well just stick your entire database in the query and dispense with table storage altogether.

Also, tates may change frequently, and you don't want your rate-updater code rewriting your PHP, it should just be able to update the database. Make it too hard to keep rates current, and you can lose a lot of money on un-covered currency movements.

Having done zillions of internationalised sites there's really only one approach that graduates the academy of trial-and-error.

 

While his solution is what I was trying to work out I prefer your solution and it would obviously work very well in terms of keeping rates up to date and the server processing time for the queries.

Thanks to everyone who contributed.

 

If you attempt to educate like that every time instead of resorting to "you're crazy do it my way", you're less likely to be mauled by a tiger.

 

hahaha I agree! Who can be bothered educating the world for free though?

 

Yeah, but my contract with DP only pays me for dashing off smartarse one-liners.

 

At least it's more than what people reply with on the introduction threads!

 

Some things are better left unsaid.