CODEWITHSUNDEEP
sqlsqlite
Daniele B
Daniele B

Reputation: 20412

SQLite: order so that results with same starting letter would come first?

SQLite 3.7.11

Given I have these two records in a SQLITE table, with the field called name:
"preview"
"view"

If I run the query:

SELECT * FROM table WHERE name LIKE "%" + $keyword + "%" ORDER BY name

with $keyword set to "vi"

I would get the results in this order:
1) "preview"
2) "view"

Is there a way to order so that the names whose first letter is the same as the first letter of the keyword (in this example "v") would come first?

Upvotes: 1

Views: 596

Answers (2)

juergen d
juergen d

Reputation: 204756

You can sort by the position of your keyword in the search string

SELECT * FROM your_table
WHERE name LIKE concat('%', '$keyword', '%')
ORDER BY POSITION('$keyword' IN name)
         name

Upvotes: 2

wvdz
wvdz

Reputation: 16641

Not sure if it can be done less verbose, but this should work. I used ? as a placeholder for your variable. The query as you posted it doesn't seem to be correct SQL.

SELECT * FROM table1
WHERE name LIKE '%' || ? || '%'
ORDER BY (CASE WHEN SUBSTR(Name,1,1) = SUBSTR(?,1,1) THEN 0 ELSE 1 END), 
         name

Upvotes: 1

Related Questions