Getting query results in a specific order

Get Help
1

I need to export out a CSV for a huge batch of cataloged tissues that are arranged in boxes in a random order, with the goal to give a spreadsheet with the GUIDs of these tissues to our genetic resources manager so he can get them into the LN tanks and then in his database. I give him a spreadsheet of tissue boxes that follows the order of the tissues in the box so he can easily find them and put labels on them. I am using the “IN” operator to include only those numbers I am interested in, but my problem is my query results have the catalog numbers in either ascending or descending order, not in the order I put them in. If I search on another identifier in our database (“ancillary Collection ID”, similar to collector number)- that result is not in any ascending/descending order as far as I can tell (this identifier is a letter followed by a series of 4 numbers), but it is also not the order I put in the query. Is it possible to get query results that respect the order put in using an “IN” operator? Or should I be building this type of query differently? This is being done in the latest instance of Sp7 (but running the same query in Sp6 gives me the same results).

3

Hi @verity,

Thanks for reaching out and for explaining your workflow! I can see why you’d want the query results to match the physical order of the tissues in your boxes.

You’ve encountered the expected behavior of databases when handling these types of requests. As you described, when you use an operator like “IN” with a list of values, the query builder disregards the provided list and sorts by the individual value of the field, meaning each GUID is sorted independently.

@emenslin has written up your request on GitHub for our development team to look into adding a “preserve input order” option to queries in a future release. You can follow its progress here:

At the moment, I am not sure if there are any viable workarounds in Specify to keep the ‘random’ ordering of the tissue boxes preserved when running the query without capturing the order in another field. Thank you for your suggestion!

In theory, you could build searches directly on the database using SQL, along with the appropriate ORDER BY statement to preserve the order of your search, but this would likely require additional assistance from IT.