SQL2 ORDER BY - List resources in last if they don't have ORDER BY property | Community
Skip to main content
G
girtorapankaj
Level 2
September 21, 2022

SQL2 ORDER BY - List resources in last if they don't have ORDER BY property

  • September 21, 2022
  • 2 replies
  • 1527 views

Hi,

 

I want to sort my result in ascending order on the basis of property (which is of double type).

And, if any resultant resource does not have that property should be listed later after resources which are having this property.

For eg. If there are three resources A,B & C, having property value as below:

Resource Name Property Name Property Value
A ranking 1.4
B property does not exist  
C ranking 1.2

 

My output should be C A B.

Currently, If I use DESC after my ORDER BY clause, I am getting A C B

And if I am using ASC after ORDER BY clause, I am getting B C A.

 

Could you please help me out to sort on the basis of ranking as per ASC order and if result does not have property should appear after those resources which are having this property.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

2 replies

Saravanan_Dharmaraj
Community Advisor
Saravanan_DharmarajCommunity Advisor
Community Advisor
September 21, 2022

I would do the sorting in the code. if the property is not there, add that resource in the separate list say list1. Sort the ones which has the property in code and then append the items finally from the list1 and return the results. It gives the flexibility. 

    G
    girtorapankajAuthor
    Level 2
    September 22, 2022

    Hi @saravanan_dharmaraj ,

     

    I know about this solution and it would work. But It will impact the performance. For the same content, you will be executing query twice. It would be last solution if I don't find anything else.

    Again, thanks for the suggestion.

      Saravanan_Dharmaraj
      Community Advisor
      Saravanan_DharmarajCommunity Advisor
      Community Advisor
      September 22, 2022

      You don't need to execute the query twice. I meant to run the query once without using ORDER By clause and do the sorting of the resulted resource by code.

      arunpatidar
      Community Advisor
      arunpatidarCommunity Advisor
      Community Advisor
      September 22, 2022

      The results are expected if there is no property and its double/long type then the value would be treated as 0.0

      Arun Patidar
        G
        girtorapankajAuthor
        Level 2
        September 22, 2022

        Hi @arunpatidar ,

        No It is not treated as 0.0. As I have provided negative value to the property and its still coming after those resources which are not having this property.

        I am looking for some option like ASC NULLS LAST. It exists in SQL, not sure do we have similar kind of option in SQL2?

        arunpatidar
        Community Advisor
        arunpatidarCommunity Advisor
        Community Advisor
        September 22, 2022

        Hi @girtorapankaj 
        I don't think all sql features are supported by jcr sql, you can try cast function if that helps.

        https://jackrabbit.apache.org/oak/docs/query/grammar-sql2.html 

        Arun Patidar
        Terms & ConditionsAccessibility statement

        Loading footer...