Subquery in ORDER BY statement

[Feanaro]

Hello,

I can't solve the following problem:
I have 2 tables

1: Currency C

  name  value
1 EUR   1,0000   (Euro
2 USD   0,8957   (US Dollar)
3 GBP   0,6453    (British Pound)

2: transactions T
  text  price  currency
1 abc   111    EUR
2 def   222    USD
3 hij   333    USD
4 klm   444    EUR

I can now easy select table with the price in any currency i like:
SELECT T.*, 
       (SELECT 
        (SELECT T.price/C.value FROM C 
         WHERE C.name=T.currency 
         )*C.value FROM C WHERE name='GBP') AS value_in_british_pound

  text  price  currency  value_in_british_pound
1 abc   111    EUR       123,123
2 def   222    USD       234,234
3 hij   333    USD       345,345
4 klm   444    EUR       456,456
 that works perfect

However, sorting by value_in_british_pound does not work
ORDER BY value_in_british_pound  --->Syntax Error
ORDER BY (SELECT 
        (SELECT T.price/C.value FROM C 
         WHERE C.name=T.currency 
         )*C.value FROM C WHERE name='GBP')
 ---> Syntax Error
ORDER BY (SELECT T.price/C.value FROM C 
         WHERE C.name=T.currency )
 ---> Syntax Error

I tried in Access and in ASP

How can I use a SELECT in an ORDER BY clause? Is there another way to do it, if possible without a stored procedure? Any help would be great because i need this feature badly for my work.

Many thanks

p.s.
Are there any good guides on how to create querys for multiple many to many relationships? I have four m-to-m relationshipsin my database and it is driving me nuts to handle them

 

[THoey]

Try thinking of it this way:

Given #1 - Result Value from query = T.price/C.value

Given #2 - In any one query, you are always going to be converting to one certain currency. (Dividing each T.price by the same C.value)

Given #3 - The value of the currency is going to be a consistant rate at the time of this query. (Value to divide by (C.price) will be the same for the whole query)

Assume - That to put these values in order, you can use the T.price in the ORDER BY clause.

Now, unless I have made a wrong conclusion, or I've had too many brain cells killed from too many beers, that should work. I didn't try it on my own.

Let me know if that helps...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[karluk]

Most versions of SQL allow referencing the column by position. In your case the subquery is in position 4 of your output. Try using the clause 'order by 4'. There's a good chance it will work.

 

[MalcolmW]

I'd vote for the numbered column solution - a standard SQL solution, and quite tidy.
Regarding resources for SQL,
SQL for Smarties by Joe Celko is good reading. Plus, ya gotta love the title.
Malcolm

 

[Feanaro]

Thanks a lot for your tips!
Sorry for the late answer but i just solved the problem today.
Here is what i did

I tried the trick with the numbered column. It worked with any normal column exept the SQL-generated one. if the first column was SQL-generated like the value_in_british_pound then it simply ignored the "ORDER BY 1" statement. So this did not work

I tried to put the whole query in a VIEW and then tried "SELECT * FROM view1 ORDER BY value_in_british_pound"
In Access this query simply did not execute but without any error message. In ASP this crashes the server :-(

Sorry, but i could not realise the tips from the first reply since i could not understand them entirely. maybe my English is to bad. From what i undertood i think '2 is not quite rigth since each price can have a different currency

I solved the problem be creating a new column value_in_euro that is updated automaticaly when an entry in currency or transactions is changed or created. this solution is unelegant and hard to manage but it works for now. If anybody knows a better solution i would gladly use it.

Thanks a lot nevertheless.

 

[MalcolmW]

instead of SELECT *
try
SELECT text, price, currency, value_in_british_pound
FROM view1
ORDER BY 4

it shouldn't make a difference, but hey, its Access. Malcolm