There is a table with a set of cities. I try to make a SELECT query so that certain cities are displayed as the very first as a result. At the same time, their order should not be disturbed (that is, as indicated in the query, in that order and should be displayed)

I tried this:

 SELECT DISTINCT city,city_url FROM cities ORDER BY city IN ('Москва', 'Санкт-Петербург', 'Самара', 'Новосибирск', 'Нижний Новгород', 'Казань', 'Челябинск', 'Омск', 'Ростов-на-Дону', 'Уфа') DESC

These cities are derived from the very beginning, but not in the order in which it is necessary.

Screen of query results when using the @ilyaplot answer and the option specified in the question. enter image description here

In this case, if you remove DESC or add ASC , then the sorting will be done just alphabetically.

  • I see on the screen that sorting by value has been applied, but in the reverse order. - ilyaplot

1 answer 1

Use sorting by value

 SELECT DISTINCT city,city_url FROM cities ORDER BY FIELD (city, 'Москва', 'Санкт-Петербург', 'Самара', 'Новосибирск', 'Нижний Новгород', 'Казань', 'Челябинск', 'Омск', 'Ростов-на-Дону', 'Уфа')

But I recommend adding the sort_order field, writing an int to it and sorting it. So it is not necessary for each request to transfer the sort order.

  • Nothing has changed: (By the way, I used this solution and it worked only when transferring the list to WHERE - Vlad
  • Is the city name in the city field? - ilyaplot
  • one
    Strangely, I used to do sorting by value exactly this way, it worked fine for me - ilyaplot
  • one
    and you could not show what order - Xramovnic
  • one
    Removed DESC from request. Try it and you. - ilyaplot 2:58