Search code examples
mysqlcasesql-like

mysql like query and sortby position of the letter

I have query similar to the question in the link given below.

https://dba.stackexchange.com/questions/60137/mysql-is-it-possible-to-order-a-query-by-a-specific-letter-using-order-by?newreg=c7d05bbfb2db401082ff650715882016

problem is that , the query returns all rows irrespective of searched term. Not only I get pname starting with D, but even all those which do not have D, only success is that results have rows which have starting letter as 'D' are on the top of the result. How can I avoid that?

Solution

  • Have you tried something like this (according to post you have mentionned) :

        SELECT 
        pname, pdescription, price
    FROM
        products
    WHERE pname LIKE '%D%'
    ORDER BY 
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;

    Pay attention to WHERE clause