How do I keep only 1 result according the alphabetical order in a tie with SQL queries?

Question

The question asked to take the city with the highest sum of goods that bought by customer for each country. Basically, there are cities that have the same number of goods, but we only keep the first one in alphabetical order. The result only contains country name, the city with highest number of goods and their goods in sum.

Table Schema:

Country table:
country_name
city_name

Goods table:
city_name
user_id
number_of_goods

My queries result:

France            Paris        85
Germany           Berlin       100
Germany           Frankfurt    100
Germany           Luxembourg   100
Netherlands       Amsterdam    75
Spain             Barcelona    93

The right result should be:

France            Paris        85
Germany           Berlin       100
Netherlands       Amsterdam    75
Spain             Barcelona    93

Answer 1

You can use row_number() :

select t.*
from (select t.*, row_number() over (partition by country order by city) as seq,
             max(no_goods) over (partition by country) as max_good
      from table t
     ) t
where seq = 1;

Answer 2

use aggregation functions min() for city and max() for no_of_goods.

select t1.country, t1.no_of_goods, min(t2.city) as city 
from
(select country,  max(no_of_goods) as no_of_goods from tableA
group by country) t1
left join tableA t2 on t2.no_of_goods = t1.no_of_goods and t1.country = t2.country
group by t1.country, t1.no_of_goods

see dbfiddle.

Answer 3

Basically, there are cities that have the same number of goods, but we only keep the first one in alphabetical order.

Based on your sample data, all cities in a country seem to have the same number_of_goods. If so, you can just use aggregation:

select c.country, min(c.city_name), max(number_of_goods)
from countries c join
     goods g
     on c.city_name = g.city_name
group by c.country;