Labkey functions srivastava aman2  2018-01-08 01:32
Status: Closed
 
Hi All,

I want to partition duplicate data in labkey. I am trying Row_Number & Rank().

For ex: RANK() OVER (PARTITION BY field1ORDER BY field2).

But, it is non supported function of labkey. Please suggest any alternatives for this?
 
 
Jon (LabKey DevOps) responded:  2018-01-09 12:44
Hello,

At this time, there really isn't anything within LabKey SQL that could give the specific functionality of Rank().

There is the typical OrderBy function, but that's not exactly the same thing.

It is possible for you to create your own code using Javascript or Java to render the data that gets generated and have the values render as if you were implementing a Rank() function. Here's a good example that you should be able to build a working concept against: https://stackoverflow.com/questions/14834571/ranking-array-elements

I've submitted a feature request to review the potential of having this added into LabKey SQL for a future build.

Regards,

Jon
 
dabaker3 responded:  2018-10-18 14:26
I agree that this is needed.
We have the same issue with duplicate data, and ordered data that could be solved very quickly with the Rank() over(partition by ) function.
Instead, we are using Javascript /Java work arounds (that are not thought all the way through and buggy) and it is a maze to troubleshoot and difficult to dedicate ownership. It hurts the modularity of our set up when we have solutions that can be solved efficiently and completely database side, but can't because of the lack of functionality.
 
Jon (LabKey DevOps) responded:  2018-10-18 14:52
Thanks for your interest and insight David!

I'll add a feature request within your Support Portal to reflect this and tie it back to the previous feature request I made from before: https://www.labkey.org/home/Developer/issues/issues-details.view?issueId=32716

Regards,

Jon