Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE cities (
- id SERIAL PRIMARY KEY,
- englishName VARCHAR(255),
- priority integer
- );
- INSERT INTO cities (englishName, priority) values('Goodnews Bay', 1);
- INSERT INTO cities (englishName, priority) values('New york', 2);
- INSERT INTO cities (englishName, priority) values('Tokyo', 3);
- INSERT INTO cities (englishName, priority) values('Tehran', 4);
- INSERT INTO cities (englishName, priority) values('Karbala', 5);
- INSERT INTO cities (englishName, priority) values('Newcastle', 6);
- SELECT * FROM cities
- WHERE englishName ilike '%New%'
- ORDER BY priority ASC, englishName ASC;
- # Result would be this:
- # id | englishname | priority
- #----+--------------+----------
- # 1 | Goodnews Bay | 1
- # 2 | New york | 2
- # 6 | Newcastle | 6
- # But I need to get this:
- # id | englishname | priority
- #----+--------------+----------
- # 2 | New york | 2
- # 6 | Newcastle | 6
- # 1 | Goodnews Bay | 1
- # How Can I do this?
- # First solution but with a little bug:
- SELECT *, POSITION(LOWER('NEW') IN LOWER(englishname)) AS indexOfTheSearchedString FROM cities
- WHERE englishname ilike '%NEW%'
- ORDER BY priority ASC, indexOfTheSearchedString DESC;
- # id | englishname | priority | indexofthesearchedstring
- #----+--------------+----------+--------------------------
- # 1 | Goodnews Bay | 1 | 5
- # 2 | New york | 2 | 1
- # 6 | Newcastle | 6 | 1
- # I care about priority more than indexofthesearchedstring, But it will blow everything up. Do have a better solution?
- # Final solution:
- SELECT *, POSITION(LOWER('NEW') IN LOWER(englishname)) AS indexOfTheSearchedString FROM cities
- WHERE englishname ilike '%NEW%'
- ORDER BY indexOfTheSearchedString ASC, priority ASC;
- # Description: We need to show cities which starts with 'NEW', And then we need to prioritize them by their priority.
- # So we just should sort by ascendingly by indexOfTheSearchedString first and after that if there were any city with the same indexOfTheSearchedString we have to look at the priority and sort by it. :)
Add Comment
Please, Sign In to add comment