Order by with the 1st city in a country in group

Databases
1

code Korea Seoul
(1) Korea Pusan
(2) Germany Berlin
(2) Germany Hamburg
(3) China Peking
(3) China Shanghai
(4) France Marseille
(4) France Paris
(5) Italy Vatican
(5) Italy Rome
(5) Italy Venice
(6) Spain Madrid
(7) Egypt Cairo
(8) Japan Tokyo
(8) Japan Nagoya[/code]I have myTable like the above.

The code below produces the result below.

[code]SELECT id, country, city FROM myTable group by id order by city

(2) Germany Berlin
(7) Egypt Cairo
(6) Spain Madrid
(4) France Marseille
(3) China Peking
(1) Korea Seoul
(8) Japan Tokyo
(5) Italy Vatican[/code]It seems correct order but as looking at it carefully its order is not correct. It is actually somewhat arbitrary…

I like to make the order with the first city basis in each country.
and compare the first city of each country(second city should be ignored).

myTarget result would be like the follow.

code Germany Berlin
(7) Egypt Cairo
(6) Spain Madrid
(4) France Marseille
(8) Japan Nagoya
(3) China Peking
(5) Italy Rome
(1) Korea Seoul[/code]How can I produce my target result above with your help?

2

first – learn how to use GROUP BY correctly

if you have GROUP BY id, then you cannot put country and city into the SELECT list unless inside an aggregate function

so this is incorrect –

SELECT id, country, city FROM myTable group by id order by city

but this is correct (if dubiously useful) –

SELECT id, MAX(country), MIN(city) FROM myTable group by id order by city

second – learn that there is no sequence of rows in a relational database table

so if you have these rows –

(5) Italy Vatican (5) Italy Rome (5) Italy Venice

then you cannot say which one comes first, because there is no sequence

once you learn these things, please ask your question again

1 Like
3

Thank you very much (I got the target result by your help)

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.