APEX

APEX 24.2.1 Autonomous

I know this topic has been covered, but unfortunately I am still at a lost!

I have a Classic Report, being used as part of a Smart Filter search. The SQL query has an ORDER BY surname clause that is being ignored. It is ordered by a different column (plain text based on a lov).

When I run the sql in SQL Developer the data is ordered as expected.

1. Sorting is disabled for all columns (sequence & sort order). Enabling it gives an error message as expected.
2. I have purged all user preferences in Workspace Administration
3. An example of my query below. Surname is a hidden column and only the full_name is shown in the report. I've put surname back in to see if it will resolve the issue if the column is visible. It didn't.

Am I missing something?

Thank you.

SELECT person_id,
       full_name,
       surname,
       church_id
       --some more columns
       --some CASE using status columns         
  FROM (
        SELECT p.person_id,
               p.first_name || ' ' || p.surname AS full_name,
               p.surname,
               c.church_id
               --more data from other tables
               --some function calls to determine status columns 
          FROM schema_name.person p
          --some more joins including table containing church_id
        )
-- WHERE additional where clause based on signed in user
 ORDER BY surname