How to make sort case insensitive while displaying the lower+upper case of values in the dropdown

Question

I am trying to convert my query result to and order them according to lowercase in asc order. But while displaying I need to preserve all the original case (lower and upper case).

My query looks like this -

SELECT distinct (p.product_id) as product_id ,lcase(p.product_name) as product_name 
from library TL, product p
WHERE TL.id = PL.id 
AND pL.product_id = p.product_id 
AND pL.product_id NOT IN (0)
order by lcase(product_name) asc;

But when I display it changes the entire dropdown values to lowercase. This is not what i want. I just want it to be displaying according to the order by lowercase.

How can I do it?

As far as I know, my orderby should have the same condition as the select isnt it?

How to make sort case insensitive while displaying the lower+upper case of values in the dropdown

Answer 1

You can try this,

SELECT product_id, product_name FROM (
    SELECT distinct (p.product_id) as product_id ,(p.product_name) as product_name,  
    lcase(p.product_name) as pname
    from unified.techlibrary TL, unified.techlibraryprod PL, unified.product p, unified.contenttype CT 
    WHERE TL.id = PL.id 
    AND pL.product_id = p.product_id 
    AND tl.contenttype_id in (1,3)  
    AND pL.product_id NOT IN (0)
) AS x order by x.pname

Hope it will help.