AnsweredAssumed Answered

Executesql() custom sort

Question asked by justinc on Aug 15, 2016
Latest reply on Aug 18, 2016 by user19752

Is there a way to get the results of an ESQL statement to be sorted by a custom-defined order?  I have a table that is used to define a value list.  I'm making a graph of data that uses that value list to define a 'State' value.  I would like the graph labels to show up in the same order as they are listed in the table, which is not alphabetical.  This external table for the value list employs the invisible BOM trick to sort the values in a particular way, based on the order of the records.  So the order is something like "To Do", "Ready", "In Progress", etc.


I have attempted a few different forms of doing this inside the SQL query itself.  (There are two queries - one to select the labels, one to select the values.)  Some examples suggest using a 'Case...' statement in the "Order By" portion of the query where the names can be manually defined:

ExecuteSQL (

        "SELECT CurrentStage "

        & " FROM TableA"

        & " WHERE ta_ID = ? "

        & " GROUP BY  CurrentStage "

        & " ORDER BY Case when Currentstage = 'To Do' then 1

                              when Currentstage = 'Ready' then 2

                               ... "

    ;"";""; 1234 )




Another suggestions was to do a join to the value-list table and then sort by that field (yes, they are the same datatype):

ExecuteSQL (

        "SELECT COUNT(*) "

        & " FROM TableA, VLTable "

        & " WHERE ta_ID = ? AND TableA.CurrentStage = VLTable.Stage "

        & " GROUP BY  CurrentStage "

        & " ORDER BY VLTable.Stage "

;"";""; 1234 )



But neither of those techniques was working.  Anyone have other suggestions?