query fails "unknown column" but I need this "column"!

ppowell

SELECT
IF(image_location_country IS NULL OR image_location_country = '', 'x', NULL) AS image_location_country_alt,
image_path

FROM image

WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'

ORDER BY upper(image_location_country_alt) desc,
upper(image_location_country) DESC,
upper(image_location_state) DESC,
upper(image_location_city) DESC,
upper(image_name) DESC,
upper(right(image_name, 2)) DESC

LIMIT 1

This query produces the following error:

Unknown column 'image_location_country_alt' in 'order clause' using query

I'm sorry but I must have this "column" in the query, it's vital for required sorting order (you have to sort image_location_country in alphanumeric order, however, that column can also be null, BUT all NON-NULL fields MUST BE FIRST before all NULL fields!)

I'm not sure what's happening, please help!

Thanx
Phil

ClarkF1

First things first, you're sure the column is in that table in the database?

I know it sounds stupid, but I've done it several times.

ppowell

It's not an actual table field, it's an alias for an IF() clause:

SELECT id, image_name, image_path, image_location_city, image_location_state, image_location_country,
IF(image_location_country IS NULL OR image_location_country = '', 'x', NULL) AS image_location_country_alt
FROM image

Phil

ppowell

No resolution but I do have an interesting update via http://groups.google.com/group/mailing.database.mysql/browse_frm/thread/e20b3cea14b5d011/ad1ee1c5e640033a?lnk=arm& (Thanks Bill!).. apparently it's a "re-broken" MySQL bug!

Phil