jitendrapurohit jitendrapurohit - 1 year ago 194
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

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 Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download