Alphabetically sort MYSQL result by letter ignoring the prefix 'The '

Question

I can sort the whole thing A-Z and it works fine, but if i try and sort by just 1 letter, it doesnt work..

For instance:

"SELECT *,TRIM(LEADING 'the ' FROM LOWER(title)) AS title_x FROM table ORDER BY title_x ASC"

this works fine and sorts/fetches the results A-Z disregarding the 'The '.. But:

"SELECT *,TRIM(LEADING 'the ' FROM LOWER(title)) AS title_x FROM table WHERE title_x LIKE 'A%' ORDER BY title_x ASC"

doesnt work, it just says "Unknown column 'title_x' in 'where clause'"

Can anyone help me correct this? Thanks.

UPDATE

For anyone else having this problem, changing the WHERE for HAVING makes this work perfectly.

you can't use a column in a WHERE clause (as an alias) if the column itself doesn't exist. — Funk Forty Niner, Apr 30 '16 at 23:40

score -2 · Answer 1 · answered Apr 30 '16 at 23:51

-2

i think its not possible to use alias in where clause. You can use sub-query or nested query to solve this issue.

answered Apr 30 '16 at 23:51

Atul Jindal

That was established in comments. Did you have any code to back this up with? Your question is of low-quality right now and is more of a comment really. – Funk Forty Niner Apr 30 '16 at 23:54

1 Answers1