Sqlite Like & Order by Match Query

SQLite LIKE & ORDER BY Match query

Try in this way :

SELECT name 
FROM table 
WHERE name LIKE "%John%" 
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10 ;

SQLite SELECT...ordering by best match

Use the expression:

(a=1) + (b=2) + (c=3) + (d=4)

in the ORDER BY clause:

SELECT *
FROM test
ORDER BY (a=1) + (b=2) + (c=3) + (d=4) DESC
LIMIT 1

Each of the terms: (a=1), (b=2), (c=3) and (d=4) evaluates to 1 for TRUE or 0 for FALSE.

See the demo.

If you want ties in the results use a CTE:

WITH cte AS (
  SELECT *, (a=1) + (b=2) + (c=3) + (d=4) counter
  FROM test
)
SELECT a, b, c, d 
FROM cte
WHERE counter = (SELECT MAX(counter) FROM cte)

See the demo.

Or with RANK() window function:

WITH cte AS (
  SELECT *, RANK() OVER (ORDER BY (a=1) + (b=2) + (c=3) + (d=4) DESC) rn
  FROM test
)
SELECT a, b, c, d 
FROM cte
WHERE rn = 1

See the demo.

Results:

| a   | b   | c   | d   |
| --- | --- | --- | --- |
| 1   | 2   | 3   | 4   |

SQLite sort only by first WHERE match

I think you want the matches to name first:

SELECT *
FROM users
WHERE name LIKE '%Kevin%' OR keyword LIKE '%Kevin%'
ORDER BY (CASE WHEN name LIKE '%Kevin%' THEN 1 ELSE 2 END),
         weight DESC;

How to use MySQL like with order by exact match first

You can do:

select *
from table t
where col like '%Ami%'
order by (col = 'Ami') desc, length(col);

SQLite query to select matching rows and 2 next with highest IDs

If the ids are sequential with no gaps (as in your example), you can do:

select st.*
from scans_table st join
     (select st2.id
      from scans_table st2
      where bssid = ? and rssi = ?
     ) ids
     on st.id in (ids.id, ids.id + 1, ids.id + 2);

This becomes trickier if the ids are not sequential. The simplest method might be to implement a lag():

select *
from (select st.*,
             (select st2.bssid
              from scans_table st2
              where st2.id < st.id
              order by st2.id desc
              limit 1
             ) as prev_bssid,
             (select st2.rssi
              from scans_table st2
              where st2.id < st.id
              order by st2.id desc
              limit 1
             ) as prev_rssi,
             (select st2.bssid
              from scans_table st2
              where st2.id < st.id
              order by st2.id desc
              limit 1, 1
             ) as prev2_bssid,
             (select st2.rssi
              from scans_table st2
              where st2.id < st.id
              order by st2.id desc
              limit 1, 1
             ) as prev2_rssi
      from scans_table st
     ) st
where (bssid = ? and rssi = ?) or
      (prev_bssid = ? and prev_rssi = ?) or
      (prev2_bssid = ? and prev2_rssi = ?);

Of course, if you are using the most recent versions of SQLite, you can use window functions, which makes this much easier:

select *
from (select st.*,
             lag(bssid) over (order by id) as prev_bssid,
             lag(rssi) over (order by id) as prev_rssid,
             lag(bssid, 2) over (order by id) as prev2_bssid,
             lag(rssi, 2) over (order by id) as prev2_rssid
      from scans_table st
     ) st
where (bssid = ? and rssi = ?) or
      (prev_bssid = ? and prev_rssi = ?) or
      (prev2_bssid = ? and prev2_rssi = ?);

Sorting search results (from Android SearchView query) by number of matches

i found a "workaround" for this problem.

After investigating different ways to write sqlite code i ended up just adding a new table column just for sorting. This column simply stores an integer and is updated every time that the user performs a search, right before the CursorLoader is created

Advantages:

• We can now do all of the relevance calculations in Java code

Drawbacks:

• Relevance calculation is done as the search is done so if we have a large number of items it may take some time to process everything

Search multiple words and intelligent sorting in sqlite

You can sum the boolean expressions in your WHERE clause and order descending:

SELECT * 
FROM myTable 
WHERE myColumn LIKE 'word1'  OR myColumn LIKE 'word2' OR myColumn LIKE 'word3' 
ORDER BY ((myColumn LIKE 'word1') + (myColumn LIKE 'word2') + (myColumn LIKE 'word3')) DESC

Note that if you want exact match of the words you should use = instead of LIKE, but if you want partial match you must concatenate the % wildcard:

myColumn LIKE '%' || 'word1' || '%'

or:

myColumn LIKE '%word1%'

---

Related Topics