1
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.
It worked @Gbrant! Thanks for contributing :)
– Aryana Valcanaia