6

I noticed that SOQL with ORDER BY and NULLS LAST ASC returns results in wrong order (especially when I order by field from relationship). I wonder if it is some kind of bug or I've done some mistake in my query syntax?

Following Example:

List<PermissionSet> permSets = [
    SELECT 
           Name, 
           Label, 
           Id, 
           Description, 
           License.Name, 
           LastModifiedDate, 
           LastModifiedBy.Name 
    FROM 
           PermissionSet 
    ORDER BY License.Name ASC NULLS LAST LIMIT 100
];

for (PermissionSet permSet : permSets) {
    System.debug(permSet.License.Name + ' ' + permSet.License);
}

Return following results:

enter image description here

0

I tried your query in me DEV org and seems that it's displaying results correctly.Debug logs

Debug logs

  • Thanks @enrik-hysko, but I traied on my three DEV orgs and results always were in uncorrect order – gpoluch Jul 6 '18 at 14:30
  • Btw. on your attached screens I can't see licenses like Sales Console User, Sales User, CRM User. That problem might be connected with specific records? – gpoluch Jul 6 '18 at 14:38
  • I'm not sure, I think that licenses that you have mentioned are maybe because you have configured their corresponding profiles on your org – Enrik Hysko Jul 6 '18 at 14:56
  • Thats licenses are Permission Set Licenses and are correspond to Permission Sets @enrik-hysko – gpoluch Jul 6 '18 at 15:04
  • 1
    and to remain to your question, it's actually strange that you are getting results in wrong order. Maybe you should raise a case to SFDC.. – Enrik Hysko Jul 6 '18 at 15:25
0

So I changed your ORDER BY to use the LicenseId instead of using the Name field of the license. When I tried your exact query, I received the same result as you received.

List<PermissionSet> permSets = [
        SELECT
                Name,
                Label,
                Id,
                Description,
                License.Name,
                LastModifiedDate,
                LastModifiedBy.Name
        FROM
                PermissionSet
        ORDER BY LicenseId NULLS LAST LIMIT 100
];

for (PermissionSet permSet : permSets) {
    System.debug(permSet.License.Name + ' ' + permSet.License);
}

this will render the below order:

debug_output

Also you could exclude the nulls as they obviously will be useless.

List<PermissionSet> permSets = [
        SELECT
                Name,
                Label,
                Id,
                Description,
                License.Name,
                LastModifiedDate,
                LastModifiedBy.Name
        FROM
                PermissionSet
        WHERE License.Name <> NULL
        ORDER BY License.Name ASC NULLS LAST LIMIT 100
];

for (PermissionSet permSet : permSets) {
    System.debug(permSet.License.Name + ' ' + permSet.License);
}

This will give the following output:

render_order2

  • Thanks @ronnie but my requirments involve orderby License.Name and I unfortunately can't exclude nulls because I have to display all Permission Sets. – gpoluch Jul 7 '18 at 17:59
  • @gpoluch How about making your ORDER BY clause to be ORDER BY License.Name, Name ASC NULLS LAST That yielded better results for mine. – Ronnie Jul 8 '18 at 2:56
  • What do you mean by better results @ronnie? I'd expect correct results, not better than that with wrong order. – gpoluch Jul 8 '18 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.