Sorting countries by multiple starting letters

benny 0
Viewed 60

In my class in high school right now we are learning about MySQL and how to select and sort items by certain qualifications. for example

Select CountryCode from Country where code like '_W%'

this snippet of code works in my database, but there is another question which i havent been able to solve which is:

Retrieve all data for the countries beginning with the characters 'N', 'O' or 'P'. Sort them alphabetically by name.

ive understood we have to use wildcards and ive tried EVERYTHING but it just wont work, either it only displays countries with the starting letter N or a full list of "null"

my code query right now is

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

i would appreciate any help ASAP since ive got other subjects that i need to work with

The answer would look a bit like this: but a lot more detailed with more columns and rows with data like GDP, Life Expectancy and more

Name Continent
Nambia Africa
Oman Asia
Pakistan Asia
-------- --------------

Thanks to Sohail for the answer, it worked!

mysql
2 Answers

This should work as 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
Suleman Sohail0 
...name LIKE 'N%' OR name LIKE 'O%' OR name LIKE 'P%'

is not going to be very efficient, because it will be treated internally as 3 subqueries with pattern-matching. In addition the OR makes it necessary for the engine to check for duplicates - while we know there are none. While most of this (but not all) will be fixed by the optimizer, it is much better (and also more readable) to write:

select * from country
 where left(name, 1) in 'NOP'
 order by name
Wolf D.0
Related