ORDER BY substrings

Hi,

my CC nicely displays its data in the order the rows have been entered.

Now for ordering it by special fields I can enter the fields in the "ORDER BY" box; adding "desc" works nicely as well.
BUT - if I want to order them by substrings of the fields (e.g. last 4 numbers of a date like 01.01.2011) I get an error message:
I enter SUBSTR(datum,7,4) desc (as I did before in CC2) in the ORDER BY box and on an error page I get the answer
0 - SQL=SELECT * FROM `joom_chronoforms_data_BPPVortraege` ORDER BY `SUBSTR(datum`, `6`, `4)` DESC LIMIT 0,20
so CC adds some strange `-symbols where they don't make any sense.

What should I enter in the ORDER BY box to get the data sorted by substrings ?

Thanks in advance,
Doris

Hi Doris,

There are some odd quoting problems with the current version of CC. As you can see here it is adding `` round the clause as if it were a column name :-(

You might be able to work round this by adding the ORDER BY clause to the WHERE box. Note that has to include a valid WHERE clause as well 1 is enough e.g.:

1 ORDER BY SUBSTR(`datum`, 6, 4) DESC

Bob

Hi Bob,

thanks for your reply. At least now I know it's not MY fault😉

Unfortunately it does not work by adding an ORDER BY to the WHERE box.
Adding anything to the WHERE box leads to an automatic "ORDER BY cf_id".
So adding ORDER BY to the WHERE box leads to a double ORDER BY, and I get
an error message again...
(0 - SQL=SELECT * FROM `joom_chronoforms_data_Evaluation` WHERE 1 order by text_3 ORDER BY `cf_id` LIMIT 0,20)
(Tried it in several connections without the substr function to exclude the
errors using additional functions...)

Perhaps another idea ?

Kind regards
Doris

OK. Found a solution, but don't tell it anybody...

If you enter some simple spaces in the ORDER BY box it does NOT add the automatic "ORDER BY cf_id"
but just the one noted in the WHERE box. And THERE you can use functions like SUBSTR...

So should one add a "Solved" to the thread ?

Kind regards
Doris

Hi Doris,

Well found - thank you.

Bob