How/Where to insert ORDER BY command

[Rich S]

Greets, all:

I have a calculation that works as is:

ExecuteSQL ( "  

SELECT Department__lxt, COUNT ( * )  

FROM ESU7_STAFF  

WHERE Status__lxt = 'Active'  

GROUP BY Department__lxt

UNION ALL

SELECT '¶Total' Department__lxt,

COUNT (Department__lxt)

FROM ESU7_STAFF

WHERE Status__lxt = 'Active' " ; Char (9) ; ""

)

 

...but the output of departments is unsorted. (See below.) I'd like to have the departments in ascending order, but I just can't get the syntax right using ORDER BY; how/where should it go?

As always, TIA for your help!

 

 

[comment]

Before starting:

Do you really have a field with the department's name in the staff table? I would expect to see a parent table of departments, with the staff holding only a DepartmentID value.

 

[Rich S]

Indeed, there is a separate Department table, so in the Staff table there's a field that holds the foreign key and a separate field that holds the name (Department__lxt) as well. (Redundant, I know.)

Edited April 30, 2024 by Rich S

[comment]

The ORDER BY clause should come at the very end. But there is a problem here:

The sort will be applied to the entire result - including the "Total" line produced by the 2nd SELECT statement. If it starts with a ¶, it will be sorted to the top. If it starts with a "T", it will come before "Vision".

You could try and prepend a control character to force it to be the last - for example, this seems to work in my test:

ExecuteSQL ( 
"SELECT Department, COUNT (*)  
FROM Staff  
WHERE Status = 'Active'  
GROUP BY Department

UNION

SELECT CHR(127) + '¶Total', COUNT (*)
FROM Staff  
WHERE Status = 'Active' 

ORDER BY 1"
; 
Char (9) ; ""
)

However, I would rather make 2 separate calls to the ExecuteSQL() function and combine the result using the FMP calculation engine.

And of course, if you can produce the same result using a FMP native report, that's always preferable.

 

 

Edited April 30, 2024 by comment

[Rich S]

Beauty! Thanks for the tips, too.