search

Home  >  Q&A  >  body text

Multiple starting letters for sorting countries

Now in my high school class we are learning about MySQL and how to select and sort items based on certain criteria. For example

Select CountryCode from Country where code like '_W%'

This code works in my database, but there is another problem that I can't solve:

Retrieve all data for countries that begin with the characters "N", "O", or "P". sort them Arranged alphabetically by name.

I understand that we have to use wildcards and I tried everything but it just doesn't work, it either only shows countries starting with N or it shows the full list of "null"

My current code query is

SELECT * from country where name like 'N%' 'O%' 'P%' order by name

I would appreciate any help soon as I have other subjects that need to be taken care of

The answer looks a bit like this: But more detailed with more columns and rows containing data like GDP, life expectancy, etc.

Name mainland
Namibia Africa
Oman Asia
Pakistan Asia
-------- --------------

Thanks Sohail for the answer, it worked!

P粉797855790P粉797855790242 days ago507

reply all(2)I'll reply

  • P粉587780103

    P粉5877801032024-03-31 14:54:52

    ...name LIKE 'N%' OR name LIKE 'O%' OR name LIKE 'P%'

    won't be very efficient since it will be treated internally as 3 subqueries with pattern matching. Furthermore, OR makes it necessary for the engine to check for duplicates - even though we know there are no duplicates. While most (but not all) of this will be fixed by the optimizer, it's better (and more readable) to write it this way:

    select * from country
     where left(name, 1) in 'NOP'
     order by name

    reply
    0
  • P粉115840076

    P粉1158400762024-03-31 11:05:36

    This should work because you have to write name LIKE 'N%' OR name LIKE ...

    SELECT * FROM country WHERE name LIKE 'N%' OR name LIKE 'O%' OR name LIKE 'P%' ORDER BY name

    reply
    0
  • Cancelreply