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

[girtorapankaj]

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.

[Saravanan_Dharmaraj]

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. 

[girtorapankaj]

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]

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]

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

[girtorapankaj]

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]

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