jitendrapurohit jitendrapurohit - 4 months ago 16
MySQL Question

Order by clause fails if upper case specified in select

I have a query like:

SELECT DISTINCT UPPER(LEFT(title, 1)) as title
FROM <table_name>
ORDER BY LEFT(title, 1);


which fails with an error


#3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'title' which is not in SELECT list; this is incompatible with DISTINCT


This works fine when using UPPER in Order by like
ORDER BY UPPER(LEFT(title, 1));


I want to know why it fails before as order by is case-insensitive by default I guess as mentioned here http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

I'm on MySQL 5.7+ with all default sql_modes enabled

Answer

I think the error message is pretty clear. The error arises because title could either refer to the column in the table or to the column alias. The search looks first for the column, so the query is interpreted as:

SELECT DISTINCT UPPER(LEFT(t.title, 1)) as title
FROM <table_name> t
ORDER BY LEFT(t.title, 1);

I think you can fix this by using a different alias:

SELECT DISTINCT UPPER(LEFT(t.title, 1)) as new_title
FROM <table_name> t
ORDER BY LEFT(new_title, 1);