2. PHPRunner General questions
  3. ORDER BY not working in Step 4 - Edit SQL Query
This topic is locked

ORDER BY not working in Step 4 - Edit SQL Query

 		2/22/2006 6:11:56 PM
PHPRunner General questions
C
 carlosxl5 author

What I would really like to do is have it sort on the values coming from a lookup table, rather than on the join value. Suppose I have two tables COUNTRY (country_id, country_name) and CITY (city_id, country_id, city_name) - the join being country_id.
I have the list screen built and appearing for the CITY table correctly and using a lookup table wizard I can get it to show the country next to the city. But I want the output to be sorted on COUNTRY (country_name), now currently it is sorted on the join field country_id which is fine if you added all the countries alphabetically, but if you forgot a country (say 'Australia') and then added that in later it would have a country_id higher than all others and would appear at the end.
I thought I could overide this behaviour by simply going in and changing the SQL so that I could do a manual join and sort (ORDER BY). I tested the queries directly in the database, but when it comes to using PHPRunner, it adds additonal where clause syntax after my order by, hence it fails with a syntax error.
Any suggestions?

Thanks

Carlos
Admin 2/23/2006

Carlos,
do not add ORDER BY clause to SQL query - it won't work.
Type order by country_name into ORDER BY box on the same page.
C
 		carlosxl5 author 2/24/2006

Carlos,

do not add ORDER BY clause to SQL query - it won't work.
Type order by country_name into ORDER BY box on the same page.


I tried this, it does not work because because the country_name column is coming from a look-up table not the main table. You get the error:



Unknown column 'country_name' in 'order clause'
Admin 2/24/2006

Carlos,
please type order by 5, where 5 is one-based index of country_name in the query.