Sort a SELECT in Mysql by a letter

Asked

Viewed 5,283 times

0

How can I make a SELECT query in Mysql that sorts the results and initially displays those that start with a specific letter?

Ex with the letter P: Paul Patricia (1st ones starting with the letter) Filipe (2nd who have the letter in the middle)

php mysql mysqli back-end order-by

  • Select records starting with the letter: Select * From tabela WHERE nome Like "P%" - Select records that have the letter in the middle: Select * From tabela WHERE nome Like "%P%"

  • Good afternoon, try this: $sql = mysql_query("SELECT * FROM person Where name like = 'a%' AND name like = 'b%' ORDER BY ASC name");

4 answers

5

For that you can use POSITION() or LOCATE() (are synonymous)

SELECT   nome
FROM     pessoa
WHERE    LOCATE( 'p', nome ) > 0
ORDER BY LOCATE( 'p', nome );

Explanation: The position returns the numeric position of the substring in the desired string, effectively ordering by the proximity of the 'p' to the left end of the string.

Handbook:

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_locate

The important thing here is this part:

ORDER BY POSITION( 'p', nome );

because to know if p is in the string, you could use a LIKE or other function.


Alternative if you want to list also those that do not have 'p':

SELECT   nome
FROM     pessoa
ORDER BY LOCATE( 'p', nome ) = 0,
         LOCATE( 'p', nome );

See both working on SQL Fiddle.

3

Selects everyone starting with the letter P

SELECT * FROM suatabela WHERE coluna LIKE 'p%'

Middle letter

SELECT * FROM suatabela WHERE coluna LIKE '%p%'

1

Starting with letter P

..... ORDER BY CASE WHEN LEFT(NomeColuna, 1) = 'P' THEN 1 ELSE 2 END, NomeColuna

Starting with letter P and then with letter P in any position

 .... Order by case WHEN LEFT(NomeColuna, 1) = 'p' then 1 else 2 end
       , case when NomeColuna LIKE '%p%' then 1 else 2 end

or ordering better

.... Order by case WHEN LEFT(NomeColuna, 1) = 'p' then 1  else 2 end
   , case when NomeColuna LIKE '%p%' then 1 else 2 end, NomeColuna

Nothing has been said about the other records that do not contain the letter P, so I assumed they should be listed last. SQL Fiddle Example

0

You can do this in different ways. I’ll show you two methods: 1 - Through Union

Select Nome,1 as ordem from tabela where Nome like 'P%'
union
Select Nome,2 as ordem from tabela where Nome like '%p%'  and Nome not like 'P%'
order by ordem, nome

The first Select brings the names starting with P and added a fixed field with heat 1.
The second Select brings names that have P in any position and that do not start with P.

Then just do the union and order through the countryside Ordem and Nome

See working on SQL Fiddle Example 1.

2 - Using a Case in the clause Order By

Select Nome from tabela where Nome like '%P%'
order by case when left(nome,1)='P' then 1 else 2 end, nome

First we select only names that have the letter p in any position and then create a sort criteria using Case. For names with P in first position the value is 1 if 2.

See working on SQL Fiddle Example 2.

Note:

Contrary to what has been said in other replies like '%p%' does not return names that have P in the middle of the word. This comparison brings names with P at any position. See: SQL Fiddle Example 3

Browser other questions tagged php mysql mysqli back-end order-by

You are not signed in. Login or sign up in order to post.