Cannot sort the results of a query with SELECT * ?: /home/Support/LabKey Support Forum

Cannot sort the results of a query with SELECT * ?

LabKey Support Forum (Inactive)

View Message

Cannot sort the results of a query with SELECT * ?

Leo Dashevskiy

2013-05-20 12:06

Status: Closed

Hey, guys!

If I use:

SELECT
Name
FROM
FCSFiles
WHERE
FCSFiles.Run.FCSFileCount != 0 AND
FCSFiles.Run.ProtocolStep = 'Analysis'
ORDER BY
Name

then the resulting table is sorted by the 'Name' column, but if I use:

SELECT
*
FROM
FCSFiles
WHERE
FCSFiles.Run.FCSFileCount != 0 AND
FCSFiles.Run.ProtocolStep = 'Analysis'
ORDER BY
Name

then the result comes back not sorted.

What's going on here?

Thanks.
-Leo

Matthew Bellew responded:	2013-05-21 12:37
This is a long-standing design problem with the LabKey query APIs. The problem is that anytime you use a query you really have two components. a) the query/table b) the view over the query. Unfortunately, these two parts don't cooperate very well, and the view definition's sort order will over ride the "ORDER BY" in the sql. In the first case since there's only one column the view is choosing to sort using it and it looks like the "ORDER BY" is working. The solution is to use the separate "config.sort" property of the API call. We should add better documentation for this with our SQL reference. Matt

Leo Dashevskiy responded:	2013-05-21 12:40
Ok, I see, thanks Matt. Yeah, I had a feeling it had something to do with the view as it was not so standard looking as for the other tables/queries... Turns out the sorting for this particular case is not important to me, but I will keep in mind the issue you described...