using subquerys in ORDER BY statements

# Thread: using subquerys in ORDER BY statements

1. Feanaro Guest

## using subquerys in ORDER BY statements

Hello,<BR><BR>I can&#039t solve the following problem:<BR>I have 2 tables<BR>
Code:
`<BR>1: Currency C<BR><BR>  name  value<BR>1 EUR   1,0000   (Euro<BR>2 USD   0,8957   (US Dollar)<BR>3 GBP   0,6453    (British Pound)<BR><BR>2: transactions T<BR>  text  price  currency<BR>1 abc   111    EUR<BR>2 def   222    USD<BR>3 hij   333    USD<BR>4 klm   444    EUR<BR><BR>I can now easy select table with the price in any currency i like:<BR>SELECT T.*, <BR>       (SELECT <BR>        (SELECT T.price/C.value FROM C <BR>         WHERE C.name=T.currency <BR>         )*C.value FROM C WHERE name=&#039GBP&#039) AS value_in_british_pound<BR><BR>  text  price  currency  value_in_british_pound<BR>1 abc   111    EUR       123,123<BR>2 def   222    USD       234,234<BR>3 hij   333    USD       345,345<BR>4 klm   444    EUR       456,456<BR> that works perfect<BR><BR>However, sorting by value_in_british_pound does not work<BR>ORDER BY value_in_british_pound  ---&#062;Syntax Error<BR>ORDER BY (SELECT <BR>        (SELECT T.price/C.value FROM C <BR>         WHERE C.name=T.currency <BR>         )*C.value FROM C WHERE name=&#039GBP&#039)<BR> ---&#062; Syntax Error<BR>ORDER BY (SELECT T.price/C.value FROM C <BR>         WHERE C.name=T.currency )<BR> ---&#062; Syntax Error<BR>`
<BR>I tried in Access and in ASP<BR><BR>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.<BR><BR>Many thanks<BR><BR>p.s.<BR>Are there any good guides on how to create html-tables for multiple many to many relationships? I have four m-to-m relationshipsin my database and it is driving me nuts to handle them<BR><BR>

2. peterjl@austec.net.au Guest

## RE: using subquerys in ORDER BY statements

I think that you could do this by creating s view in your database, and getting your asp to query that view instead of the base tables. <BR><BR>cmdSql = "Create view MyView as SELECT T.*, (SELECT (SELECT .price/C.value FROM C WHERE C.name=T.currency)*C.value FROM C ) AS value_in_british_pound"<BR>ObjConnection.Execute cmdSql<BR>Set objRs = ObjConnection.Execute "Select * from MyView WHERE name=&#039GBP&#039 Order by value_in_british_pound"<BR><BR>Please not that I am not suggesting that you use the Create View statenment every time you run the ASP. It should be done once in your db and then it will be available to all future pages.<BR><BR>Note also that I have taken the where clause out of the create view and put it in the select statement. <BR><BR>Finally I have not checked the validity of your sub-select query, as I assume that part worked.

3. Feanaro Guest

## RE: using subquerys in ORDER BY statements

Thanks a lot for your sugestion.<BR>I have tried your tip. It works great, but when i use the view together with another table (for example "SELECT view1.*, table1.* FROM view1, table1 WHERE ...) then the Server crashes :-(<BR><BR>I have found a different solution: I added a new column price_in_euro which gets updatet whenever a line in transaktion or currency is updated or created and use this to sort. It is an unelegant and crude solution and difficult to manage but it works<BR><BR>If anybody has another solution i would gladly use it

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•