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

Unrelated, but: `distinct` is **NOT** a function. It always applies to all columns in the select list. `distinct a` is the same as `distinct (a)` and consequently, `distinct a,b,c` is the same as `distinct (a),b,c` or `distinct a,b,(c)` — , May 07 '19 at 13:28

S-Man · Accepted Answer · 2019-05-07T13:37:32.410

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.

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

1 Answers1