Sorting countries by multiple starting letters

Question

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!

`where name like 'N%' or name like 'O%' or name like 'P%'`. Answered [here](https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server) — iced, Sep 06 '22 at 19:01
Its good that you provided what you've already tried, however, you should edit your question and include some sample date (not as a screenshot) but as a table, and what you expect the result set to be as well. I would also look into how to use an `OR` operator in your `WHERE` clause. — griv, Sep 06 '22 at 19:04
thank you for the feedback @GRIV with the help of a different answer in this thread, adding the OR operator only allowed it to display up to the country Oman, but not anything further. after the country of Oman and its data (GDP ++) it only displays "null" ive also tried using IS NOT NULL but then i just get the entire database without it filtering out countries that start with N, O or P — benny, Sep 06 '22 at 19:28
Ive already been to that page @iced it ends up only displaying a table with all its data showing "null" — benny, Sep 06 '22 at 19:30
No problem. I think editing your question and providing us with sample date and your expected result set will get you the answer you seek. See [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). — griv, Sep 06 '22 at 19:30

Suleman Sohail · Accepted Answer · 2022-09-06T19:30:16.307

0

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

edited Sep 06 '22 at 19:30

answered Sep 06 '22 at 19:15

Suleman Sohail

32
3

it stops at Oman, after that all i see is an empty row with "null" in it, it doesnt display any countries beginning with P either – benny Sep 06 '22 at 19:24
oh sorry i wrote '%P' instead of 'P%' – Suleman Sohail Sep 06 '22 at 19:29
Oh it worked, i didnt notice it myself haha, thank you! is there anyway to make the code a bit more clean? instead of having to write OR name Like #? only because if it were a bigger assignment i would be stuck having to repeat the code over and over again – benny Sep 06 '22 at 19:33

score 0 · Answer 2 · answered Sep 06 '22 at 21:32

0

...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

answered Sep 06 '22 at 21:32

Wolf D.

176
4

Of course it doesn't matter if there are 200 records, but it's never too early to acquire good habits ;) – Wolf D. Sep 06 '22 at 21:35
Wouldn't this approach result in a full-table scan, thus discarding any possible indexes on the `name` field? – Kate Sep 06 '22 at 22:14
If we are going to have indexes then let's have one on left(name, 1), no ? – Wolf D. Sep 06 '22 at 23:17
Ive just tried your answer, but i get the error that "select is not valid at this position for this server version, excpecting: '(', WITH. my professor has told us to use MySQL server version 8.0. maybe that has something to do with it? – benny Sep 07 '22 at 11:40
use "...where left(name, 1) in ('N','O','P')" or "... where contains ('NOP', left(name, 1))" if you are using MSSQL – Wolf D. Sep 07 '22 at 16:06

Sorting countries by multiple starting letters

2 Answers2