ORDER BY Sub-Query

thechatking
thechatking used Ask the Experts™
on
Hi,

I have a relatively complex query in Access, which contains a number of sub-queries.  I wish to sort the data by the results produced by one of these queries, but get a syntax error when trying to run it.

My basic question is 'How (if at all possible) do you order by the result of a sub-query?'

Thanks

Mark
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you post an example of your query as it stands?  I'm not sure how you've included the fields from the subquery...

Author

Commented:
It's quite a big and untidy query at the moment (Access isn't great for editing SQL), but here is the basic syntax I have used:

SELECT Col_1, (SELECT Col_2 FROM TABLE_2) AS Expr_1
FROM TABLE_1
ORDER BY (SELECT Col_2 FROM TABLE_2)

Hope this provides the information you needed.  Obviously, the queries are a lot more complex than this, but this makes for easier reading! ;o)
IT Engineer
Distinguished Expert 2017
Commented:
SELECT Col_1, (SELECT Col_2 FROM TABLE_2) AS Expr_1
FROM TABLE_1
ORDER BY 2

NOTES:
- ORDER BY 2 means, order by 2nd field on select statement
- (SELECT Col_2 FROM TABLE_2) should only return 1 record

Good luck,


Vitor Montalvao

Author

Commented:
Thanks - you're a star!

The ORDER BY 2 statement worked a treat. :o)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial