How to write select statement with a condition of case-insensitive from a select query output

Question

I'm getting data like this

• Doha
• doha
• Dubai
• Abu Dhabi
• Abu dhabi

from the query select distinct(trim(name)) as loc from locations order by trim(name)

Now I want to remove duplicates using lower() or upper() and select the first record . If I use lower function like below,

select distinct(lower(A.loc)) from( select distinct(trim(name)) as loc from locations order by trim(name) ) as A order by lower(A.loc);

it gives result converted to lower as below.

• doha

• dubai

• abu dhabi

  But I want original result as previously mentioned.

• Doha

• Dubai

• Abu Dhabi

Answer 1

demo:db<>fiddle

SELECT DISTINCT ON (lower(city))
    city
FROM
    cities

DISTINCT ON takes an arbitrary column and gives out the first one of duplicates. In this case a column is created internally with all lower case. Then the first record is taken, but only the original column.

---

NOTE You have no special order. It will be taken a random order (which is for example how data is stored internally). To achieve the upper case value you have to write:

SELECT DISTINCT ON (lower(city))
    city
FROM
    cities
ORDER BY lower(city), city DESC

demo:db<>fiddle

The ORDER BY lower(city) is necessary because the DISTINCT ON needs the given columns to be the first ordered. After that you can order by any other column. ORDER BY column DESC moves the upper cases top.