Sort order in API output for table functions

Inside the function template code?

I have done this, but the API engine seems to order on the values ofthe first column of the output table

I was able to sort the data by sorting it in the table function. How do you sort the data in the function?

;WITH a AS (
             SELECT    wrl_guid, ass_guid
             FROM      work_location 
             LEFT JOIN asset         ON ass_adr_guid = wrl_adr_guid
             WHERE  wrl_guid = @wrl_guid
           )
INSERT INTO @work_location_entity (ent_guid, ett_id, ent_description, sort_order)          
SELECT x.ent_guid, ett_id, ent_description = e.ent_dx_default, sort_order = ROW_NUMBER() OVER (ORDER BY sort_order, ent_description)
FROM   (
         SELECT ent_guid = w.wrl_guid, ett_id = 'wrl', ent_description = wrl_name,      sort_order = 0, update_date_time = ISNULL(wrl_update_date_time, wrl_insert_date_time)             FROM work_location w JOIN a ON a.wrl_guid = w.wrl_guid
         UNION                                                                                                                                                                            
         SELECT ent_guid = act_guid,   ett_id = 'act', ent_description = act_custom_id, sort_order = 1, update_date_time = ISNULL(act_update_date_time, act_insert_date_time)             FROM activity        JOIN a ON a.ass_guid = act_belongs_to_ass_guid
         UNION                                                                                                                                                                                                 
         SELECT ent_guid = acx_guid,   ett_id = 'acx', ent_description = acx_custom_id, sort_order = 1, update_date_time = ISNULL(acx_update_date_time, acx_insert_date_time)             FROM action          JOIN a ON a.ass_guid = acx_belongs_to_ass_guid
         UNION                                                                                                                                                                                                 
         SELECT ent_guid = isu_guid,   ett_id = 'isu', ent_description = isu_custom_id, sort_order = 1, update_date_time = ISNULL(isu_update_date_time, isu_insert_date_time)             FROM issue           JOIN a ON a.ass_guid = isu_belongs_to_ass_guid
         UNION                                                                                                                                                                                                 
         SELECT ent_guid = obs_guid,   ett_id = 'obs', ent_description = obs_custom_id, sort_order = 1, update_date_time = ISNULL(obs_update_local_date_time, obs_insert_local_date_time) FROM observation     JOIN a ON a.ass_guid = obs_belongs_to_ass_guid
     ) x
JOIN entity e ON e.ent_guid = x.ent_guid
ORDER BY sort_order

RETURN

This is the template of the function, copy + paste into SSMS for a clear picture

In order to sort the data inside the table valued function. Set a primary key on the sort column, you might also want to make this the first column in the table.. 

 

Ok, clear for this scenario it will work, but it will not always be like this. 

Would it not be possible to change the API engine so that it uses the same order as the output of the SP?

The API is giving the same output as the function. So calling the function from the database without a sort order would give the same result. For a future release the development team will look into a possibility to sort on an API call. For now the best option is to add the PK to force the sorting.