SQLite ordering that puts prefix matches first

I was wondering how I might formulate a SQLite ORDER that as its first criterion will separate matches depending on whether a search string was matches against a prefix or an infix of a column. The second ordering would be simply ASC.

E.g. searching for "st" and simply ordering by that column will yield:

  • Astrophysicists
  • Baker Street
  • Master Yoda
  • Stack Overflow
  • Star Trek
  • Stegosaurus
  • System Shock
  • Westwood

I'd like to order it like this:

  • Stack Overflow
  • Star Trek
  • Stegosaurus
  • Astrophysicists
  • Baker Street
  • Master Yoda
  • System Shock
  • Westwood

So prefix matches come first, then the rest. The secondary ordering is simply ASC.

Thanks for any input.

Answers


Let's say you have a Table named Products and let us just make it simple that it has only one field named Productname. And you want to search a Product name that starts with the word Co or has the word Co in between.

So, the query would be like this:

SELECT Productname FROM Products
WHERE Productname LIKE 'Co%'
UNION ALL
SELECT Productname FROM (
SELECT Productname FROM Products
WHERE Productname LIKE '%Co%'
ORDER BY Productname) B

So, if you have the following records

Coco Crunch
Lucky Cow
Coffee
Sweet Corn
Cookies
Corn Puff

It would now become like this

Coco Crunch
Coffee
Cookies
Corn Puff
Lucky Cow
Sweet Corn

Try it out with your own fields and Table.


Need Your Help

Rails - combine multiple has_many throughs

ruby-on-rails activerecord has-many-through arel

I'm just started with Rails, and have a problem I can't solve myself:

Play 2.0 templating - working with a string and then presenting it as HTML

scala playframework-2.0

In Play 2.0, with Scala, I want to make a factory that takes an html fragment and puts every line inside a <div> block. I have tried several ways that I can think of but it always comes up ei...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.