SELECT with two results

Question

SELECT with two results

Navigation

#1 by (4 votes)
#2 by (1 votes)
#3 by (-1 votes)
#4 by (-1 votes)

1

Table: PEOPLE

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

How can I make a SELECT that gives me a list with the names sorted by ID descendente , however, a certain NAME first and below the continuation?

Type: first the MARIAS and below the alphabetical continuation and ID DESC

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

In this way, not the error, but the query returns me the list normally ordered only by id DESC and not with the name MARIA at the top of the listing.

sql query select

asked by anonymous 19.01.2018 / 12:24

4 answers

Clone DataSet (TZQuery) in Delphi 7 Get date formatted in HTML5

score 4 · Answer 1

You can try to force a case in the ORDER BY clause, I already did it and it 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 the above SQL), I recommend that you only use 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 make your SQL with a better result, since the current form is not a good practice, here is the 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 names that have Maria from Ana Maria to 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.

score 1 · Answer 2

1

First it will get all the 'maria' + surname, then it returns the rest ordered alphabetically.

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

Source: link

19.01.2018 / 13:01

score -1 · Answer 3

Thanks everyone:

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 sorted by neighborhoods, first placing a certain neighborhood at the top and continuing the list with the other neighborhoods.

Leave the post to other users with the same difficulty.

Thank you all.

score -1 · Answer 4

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

You can do this through union ( UNION ) of two queries.

But for ordering to work you will need to create a column to set sort 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 this example online: SQLFiddle

Responding to your comment. You did not create the Order column in the 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