SELECT with two results

Question

Table: PEOPLE

id - nome
1 - BEATRIZ
2 - MARIA
3 - CARLOS
4 - MARIA
5 - JÉSSICA
6 - HENRIQUE

How do I make a SELECT that gives me as a result a list of the names ordered by ID descendente, however, a given NAME first and below the continuation?

Type: first the MARIAS and below the continuation in alphabetical order and ID DESC.

SELECT * FROM PESSOAS WHERE nome='MARIA' OR nome!='MARIA' ORDER BY nome, id DESC

Thus not from error, but from query returns me the list normally ordered only by id DESC and not with the name MARIA at the top of the list.

Answer 1

You can try to force a casein the clause ORDER BY, I already did and would look something like this:

SELECT * 
  FROM PESSOA  
 WHERE NOME = 'MARIA' OR NOME != 'MARIA' 
 ORDER BY CASE WHEN ((NOME  = 'MARIA') OR (NOME != 'MARIA')) THEN 0 ELSE 1 END, NOME, ID;

Edited

If you want only one record to be at the top (which will not happen in SQL above), I recommend that you use only one condition in ORDER BY. It would look something like this:

SELECT * 
  FROM PESSOA  
 WHERE NOME = 'MARIA' OR NOME != 'MARIA' 
 ORDER BY CASE WHEN (NOME  = 'MARIA') THEN 0 ELSE 1 END, NOME, ID;

Edited

To leave your SQL with a better result, since the current form is not a good practice, follows suggestion:

SELECT * 
  FROM PESSOA  
 WHERE NOME LIKE '%MARIA%' 
 ORDER BY CASE WHEN (NOME LIKE '%MARIA%') THEN 0 ELSE 1 END, NOME, ID;

In this way, we will return all the names that have Mary, of Ana Maria until Maria Braga, but in this way the first record of the return will obey the second clause, which is NOME, for example:

1: Ana Maria

2: Anna Maria

3: Maria Braga

And so on and so forth.

Answer 2

First he will take all 'maria'+surname, then he returns the rest ordered alphabetically.

SELECT * from pessoas ORDER BY nome LIKE 'maria%' desc, nome

Source: https://stackoverflow.com/a/11908079/4438007

Answer 3

If you wanted to specifically display the list of a given name and then the rest of the data, as in the example you quoted.

You can do it through marriage( UNION ) of two consultations.

But for the sorting to work you will need to create a column to define the sorting priority, as in the example below:

SELECT Id, Nome, 0 Ordem
FROM Pessoas 
WHERE nome LIKE 'Maria%'
UNION ALL
SELECT Id, Nome, 1 Ordem 
FROM Pessoas 
WHERE nome NOT LIKE 'Maria%'
ORDER BY Ordem, Nome

Follow online example: Sqlfiddle

Responding to your comment. Failed to create column Order in query:

SELECT *, 0 Ordem FROM noticias WHERE bairro_id=5 
UNION ALL 
SELECT *, 1 Ordem FROM noticias WHERE bairro_id <> 5 
ORDER BY Ordem, id DESC

Answer 4

Thanks a lot:

My problem has been solved with the query

SELECT * FROM noticias WHERE bairro_id = 5 OR bairro_id != 5 ORDER BY CASE WHEN (bairro_id = 5) THEN 0 ELSE 1 END, id DESC

The example with the table above was just to emphasize. The idea is that the system returns me a list ordered by neighborhoods, first putting a certain neighborhood at the top and continue the list with the other neighborhoods.

Ai is the post for other users with the same difficulty.

Thank you all.