Order Result in Sqlite
In MySQL , my sql is like following
SELECT * , IF( `Word` = 'sim', 1, IF( `Word` LIKE 'sim%', 2, IF( `Word` LIKE '%sim', 4, 3 ) ) ) AS `sort`
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word`
This sql is not working in SQlite. I 开发者_StackOverflow社区want to do result order.
SELECT * FROM dblist where word like 'sim' or word like 'sim%' or word like '%sim%' or word like '%sim'
equal sim is a frist , sim% is second and %sim% is a thrid and then %sim is a last.
Currently I can't sort like mysql in sqlite. How to change sql to order the result ?
You should use a CASE expression instead of IF as the latter is not supported by SQLite. Here is the SQL you need:
SELECT
*,
CASE WHEN `Word` = 'sim' THEN 1
WHEN `Word` LIKE 'sim%' THEN 2
WHEN `Word` LIKE '%sim' THEN 4
ELSE 3
END `sort`
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word`
SELECT * , case when `Word` = 'sim' then 1 else case when `Word` LIKE 'sim%' then 2 else case when `Word` LIKE '%sim' then 4 else 3 end end end as sort
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word
精彩评论