Order by in dbjoin not working

[bespokeappstt]

I have a dbjoin with a nested query. I want the multi-select drop down to order the numbers. The parent list element is rad_serialassets___serial_number and the list with the dbjoin is rad_rentals___serial. First I tried:

WHERE {thistable}.id IN (SELECT a.id FROM rad_serialassets AS a LEFT JOIN rad_rentals_repeat_serial AS b ON a.id = b.serial WHERE b.parent_id = "{rad_rentals___id_raw}") OR {thistable}.available = 1 order by "{rad_rentals___serial_raw}" DESC

The I tried

WHERE {thistable}.id IN (SELECT a.id FROM rad_serialassets AS a LEFT JOIN rad_rentals_repeat_serial AS b ON a.id = b.serial WHERE b.parent_id = "{rad_rentals___id_raw}") OR {thistable}.available = 1 order by "{rad_serialassets___serial_number_raw}" DESC

None of them work. Using {thistable}.serial throwed an error and the page didin't even load. How can I order those numbers in the drop down?

 

[troester]

1. oder by inside an IN subquery won't do anything, it doesn't matter if you have IN (1,2,3) or IN (3,2,1)
2. order by "{rad_rentals___serial_raw}": what should this do, it will become something like order by "5", you'll need a DB column name, no placeholder

Enable and use fabrikdebug to see which queries are used in the end.

 

[bespokeappstt]

I've decided to use the List settings and set the 'Order by' to use the 'Serial_number'. Though not perfect this some sorting taking place. The serial are first grouped by the first few digits. From there it's sorted in ascending order:


I also went into phpmyadmin and changed the order of the columns. This was recommended on Stackexchnage. Here is the url: https://dba.stackexchange.com/questions/140941/is-it-possible-to-reorder-tables-in-phpmyadmin The first column gets sorted in ascending order:


Now the dbjoin looks like this:


The serials are being grouped by the first few digits and then sorted. I can live with that.