Sort in SQL by prioritizing a specific field value

Question

I have a large client table, and I need to select the following fields (sample data)

id | id_ls | id_sistema_ls | nome | ativo
----------------------------------------
 1 | 1007  |    158849     |   A  |   0
 2 | 1187  |     849       |   B  |   1
 3 | 19726 |    26606      |   C  |   1 
 4 | 5718  |    98756      |   D  |   0
 5 | 6466  |     122       |   E  |   1
 6 | 11825 |      18       |   F  |   1

I own an array of id_ls which I can select, so my original query would be

SELECT id, id_ls, id_sistema_ls, nome, ativo FROM clientes WHERE id_ls IN (1007,1187,3050,5718,6466,11825,12067,12490) GROUP BY id_ls ORDER BY ativo DESC, nome ASC

This query returns me the sample result up there like this

id | id_ls | id_sistema_ls | nome | ativo
----------------------------------------
 2 | 1187  |     849       |   B  |   1
 3 | 19726 |    26606      |   C  |   1 
 5 | 6466  |     122       |   E  |   1
 6 | 11825 |      18       |   F  |   1
 1 | 1007  |    158849     |   A  |   0
 4 | 5718  |    98756      |   D  |   0

It is working correctly. However, now I need to prioritize a specific sequence of id_sistema_ls, bringing them first and only then following the ordination. For example, I want you first to be the id_sistema_ls 26606 then the 18, and then follow up the initial result. Staying this way:

id | id_ls | id_sistema_ls | nome | ativo
----------------------------------------
 3 | 19726 |    26606      |   C  |   1 
 6 | 11825 |      18       |   F  |   1

 2 | 1187  |     849       |   B  |   1
 5 | 6466  |     122       |   E  |   1
 1 | 1007  |    158849     |   A  |   0
 4 | 5718  |    98756      |   D  |   0

I have an array that can contain up to 9 numbers sorted by priority. And I need to respect it. I tried to follow this solution that I found in the stackoverflow. I did the following:

SELECT id, id_ls, id_sistema_ls, nome, ativo FROM clientes WHERE id_ls IN (1007,1187,3050,5718,6466,11825,12067,12490) GROUP BY id_ls ORDER BY FIELD (id_sistema_ls, 26606, 18), ativo DESC, nome ASC

But it didn’t work. It keeps bringing the same query result without FIELD. Can anyone tell me why? Or if there is any way I can do without slowing down the query?

The project handles thousands of results and performance is important. It is also important to keep all results in a query only due to already implemented paging.

Answer 1

This type of control seems strange to put in SQL, maybe it is more interesting to do this ordering using a more complete language.

If using SQL is really the only alternative, you can use a CASE:

SELECT id, id_ls, id_sistema_ls, nome, ativo FROM clientes
  WHERE id_ls IN (1007,1187,3050,5718,6466,11825,12067,12490)
  GROUP BY id_ls
  ORDER BY
    CASE id_sistema_ls
      WHEN 26606 THEN 0
      WHEN 18 THEN 1
      ELSE 2
    END,
    ativo DESC,
    nome ASC

It gets kinda hard to read and maintain, so I recommend trying some alternative if possible.

Answer 2

It is possible to apply a CASE rule to order by

...
ORDER BY (CASE WHEN id_sistema_ls = 26606 THEN 0
               WHEN id_sistema_ls = 18    THEN 1
               ELSE 2 END),id_sistema_ls

Answer 3

My answer is the same as the others but I show you another way to use the ORDER BY, see:

SELECT id
      ,id_ls
      ,id_sistema_ls
      ,nome
      ,ativo 
  FROM clientes 
 WHERE id_ls IN (1007,1187,3050,5718,6466,11825,12067,12490) 
 GROUP BY id_ls
 ORDER BY id_sistema_ls not in (26606, 18), ativo DESC, nome ASC

This way you can put the your array that can contain up to 9 numbers sorted by priority without having to use the case, thus not leaving maintenance difficult.

My base was that question.