nodejsdeveloperskh

select and sort by two field

Mar 25th, 2022 (edited)
183
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE cities (
  2.     id SERIAL PRIMARY KEY,
  3.     englishName VARCHAR(255),
  4.     priority integer
  5. );
  6.  
  7. INSERT INTO cities (englishName, priority) values('Goodnews Bay', 1);
  8. INSERT INTO cities (englishName, priority) values('New york', 2);
  9. INSERT INTO cities (englishName, priority) values('Tokyo', 3);
  10. INSERT INTO cities (englishName, priority) values('Tehran', 4);
  11. INSERT INTO cities (englishName, priority) values('Karbala', 5);
  12. INSERT INTO cities (englishName, priority) values('Newcastle', 6);
  13.  
  14. SELECT * FROM cities
  15. WHERE englishName ilike '%New%'
  16. ORDER BY priority ASC, englishName ASC;
  17. # Result would be this:
  18. # id | englishname  | priority
  19. #----+--------------+----------
  20. #  1 | Goodnews Bay |        1
  21. #  2 | New york     |        2
  22. #  6 | Newcastle    |        6
  23.  
  24. # But I need to get this:
  25. # id | englishname  | priority
  26. #----+--------------+----------
  27. #  2 | New york     |        2
  28. #  6 | Newcastle    |        6
  29. #  1 | Goodnews Bay |        1
  30.  
  31. # How Can I do this?
  32. # First solution but with a little bug:
  33. SELECT  *, POSITION(LOWER('NEW') IN LOWER(englishname)) AS indexOfTheSearchedString FROM cities
  34. WHERE englishname ilike '%NEW%'
  35. ORDER BY priority ASC, indexOfTheSearchedString DESC;
  36. # id | englishname  | priority | indexofthesearchedstring
  37. #----+--------------+----------+--------------------------
  38. #  1 | Goodnews Bay |        1 |                        5
  39. #  2 | New york     |        2 |                        1
  40. #  6 | Newcastle    |        6 |                        1
  41.  
  42. # I care about priority more than indexofthesearchedstring, But it will blow everything up. Do have a better solution?
  43.  
  44. # Final solution:
  45. SELECT  *, POSITION(LOWER('NEW') IN LOWER(englishname)) AS indexOfTheSearchedString FROM cities
  46. WHERE englishname ilike '%NEW%'
  47. ORDER BY indexOfTheSearchedString ASC, priority ASC;
  48. # Description: We need to show cities which starts with 'NEW', And then we need to prioritize them by their priority.
  49. # 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