Sorting results when autocomplete matches multiple columns in SQL

Question

I've run into an issue with an autocomplete field I'm working on. The field I'm working with is composed of the form "<NAME> (<CODE>)". When a user starts typing in text, I want to display any results that match either NAME or CODE.

For example, if this list contains items and their codes, like "Personal Computer (PC)", then I'd want the list to pop up that row if the user types "P", "PC", "Per", etc.

I've gotten this to work fine in SQLite with a query like this:

SELECT *
FROM table
WHERE name LIKE "?%" or code LIKE "?%"

However, the problem I'm running into now is how to best sort the results that come back from this. For example, If someone enters "PC", I want "Personal Computer (PC)" to be the first result. However, if there's another row (you'll have to bear with me as this is contrived) "PC Case (301)", then there's no simple ordering I can do on the results to ensure that the best match appears first. Ordering by name and code both returns PC Case first.

I want a query where it returns the best match first, rather than items in alphabetical order. Is there such a function I can use in SQLite to get this, or should I return the results and then mess with the order in the code?

If it helps any, I'm using this for FilterQueryProviders on Android.

Answer 1

You may want to consider switching to FULL TEXT searching and use ranking.

Answer 2

Yes, you should implement FullTextSearch and the use MATCH in your queries.
Ref: http://dotnetperls.com/sqlite-fts3

Answer 3

This is a long time after the fact, but I've solved my dilemma without having to resort to crazy sorting tactics.

Basically, once an autocomplete gets complex enough, you need to implement your own CursorAdapter (implementing FilterQueryProvider) then override convertToString(). That way, you end up being able to do complex queries via runQuery(), but can then convert it to readable form in convertToString().