Query a shapefile with ordered results

Question

In VB2010 I am using the IQueryFilter interface to hit a shapefile and get back some records. It works great but am having trouble figuring out how to get results back in an order as input by the user. They input airports in a listbox and I take that and create a where clause out of it. By default the recordset returned is in alphabetical order which will not work here as the user needs the results back in the order they input.

Dim pQryFltr As IQueryFilter = New QueryFilter
            pQryFltr.WhereClause = "APT LIKE 'DFW' OR APT LIKE 'STL' OR APT LIKE 'ORD'"

results come back as

DFW
ORD
STL

I am trying to figure out how I can return the results in an order that is equivalent to how the user input the data. Is that even possible?

~AGP

AbelPerez · Answer

Looking at my challenge I think I see probably the most efficient way to do this is to give the user the option to do it the standard way or to do individual queries for each item. At most they will have 10 items to look up. if they have more I warn them that it could take several minutes. As I loop through each item I get the recordset for that one item and add it to a grid. This way I am assured that the final items the user sees are in the order they have input into the list box. I chose this over a more complicated sorting routine since the number of input items is really not going to be that large.

RichardFairhurst · Answer

After rereading your first post I now see that you really want the order to match the order established by your where clause, which has nothing to do with the way the data was created or any field in the data.  (All of the terms you used to describe your problem are associated with user data entry directly to the database itself, not just user interaction through the query, so your original post was open to confusion).

To do that you would have to parse the where clause to get the list of unique values and then create a two dimensional array or some other collection object to preserve that order and store the cursor records to match that order.  Cursors cannot be ordered by anything other than a field (even unsorder data is actually ordered by the ObjectID/FID).

Parsing would be easier if you build a where clause with the IN operator:

"APT IN (''DFW', 'STL', 'ORD')"

Your Like expression is not any different from the above since it had no wildcards in it.  If it had used wildcards then you would have to parse the original format of your where clause and insert to the the array/collection based on comparisons that matched the wildcard format order.

In any case, no cursor can do what you want without parsing the original where clause into three separate where clauses that you run separately in the order that you parsed them.  You would have to store and output the result through some form of intermediate list/array/collection that combines them in that specific order.  You cannot return a single table view or directly bound datagrid of the full selection that will match the user input order.

What you want would operate in a way that is the exact opposite of cursor optimization, so thank God no database designer ever considered making SQL work that way.  All forms of ordering data cause cursor performance to suffer to one degree or another, so by default none is done, and only sort orders by field are permitted since those can be optimized by indexes.

RichardFairhurst · Answer

I was thinking I would have to sort it myself since the function returns a list of airport classes. I did read about Postfix Clause but I cant use an ORDER BY since there is no field that I want to order by. Its really as input by the user which can be anything really.

Thanks for the tips.

By default that is the order the cursor will read records.  It processes records in the order of the ObjectID/FID, which are determined by the creation date.  If that order is good enough and you want to remove duplicates so that only the first instance encountered determines the listing order you can use an Array or Collection to develop an unsorted list for your tool to display.

Of course if a user creates a feature with a value you want to list and then many months later overwrites that value in an edit session with a different value that won't be considered by the ObjectID order.  Also if a user splits a feature, part of that feature gets a new ObjectID and the other part has the old one, even though both can be considered new or existing depending on how you look at them.  Also if a user intended to create the records in a specific order but missed one and added it later, the cursor won't order the list the way the user intended.  Domains are most often used to maintain numeric values that are associated to substituted display values that maintains a user defined order that defies the natural sorting orders of the displayed values.

If you need anything ordered based on some specific set of rules related to record creation or modification during data editing by your users, then you need to implement some form of Attribute Assistant set up that fills in fields you can sort based on Record Creation date times and Record Modification date times.