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 8 3114 ph**************@gmail.com wrote:
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
Not quite sure what's going on here, but does this work:
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
strawberry wrote:
ph**************@gmail.com wrote:
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
Not quite sure what's going on here, but does this work:
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
No it doesn't "unknown column "isnull""
Phil ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
Not quite sure what's going on here, but does this work:
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
No it doesn't "unknown column "isnull""
Phil
Can I just confirm that you're using mysql 4.1 or later?
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
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
>
Not quite sure what's going on here, but does this work:
>
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
No it doesn't "unknown column "isnull""
Phil
Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20 ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote: ph**************@gmail.com wrote:
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
Not quite sure what's going on here, but does this work:
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
>
No it doesn't "unknown column "isnull""
>
Phil
Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20
So this query works fine:
SELECT image_path AS imagepath FROM image ORDER BY imagepath LIMIT 1;
Sorry, I'm clutching at straws now (as if you couldn't guess)
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote:
ph**************@gmail.com wrote:
strawberry wrote: ph**************@gmail.com wrote:
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
>
Not quite sure what's going on here, but does this work:
>
SELECT image_path, IF(image_location_country IS NULL OR
image_location_country ='', '1', 0) AS isnull
FROM image
WHERE image_path REGEXP '\/www\/html\/tools\/images\/myalbum/.+$'
ORDER BY isnull ASC,
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
No it doesn't "unknown column "isnull""
Phil
>
Can I just confirm that you're using mysql 4.1 or later?
Yes, 4.1.20
So this query works fine:
SELECT image_path AS imagepath FROM image ORDER BY imagepath LIMIT 1;
Sorry, I'm clutching at straws now (as if you couldn't guess)
I got it.. but the solution makes no sense. You apparently can't do
upper() on this alias because MySQL doesn't allow functions upon
aliases.
Phil ph**************@gmail.com wrote:
Unknown column 'image_location_country_alt' in 'order clause' using
query
I have confirmed the error you saw, using both MySQL 4.1.21 and 5.0.24a.
This looks identical to the problem in Bug #11694: http://bugs.mysql.com/bug.php?id=11694
If you just use the plain column alias name, it works. If you include
it in an UPPER() expression, it breaks. This is consistent with the bug
described in Bug #11694.
That bug was supposed to have been fixed as of MySQL 4.1.16. But it
appears to have regressed (that is, re-broken).
Regards,
Bill K.
Bill Karwin wrote:
This looks identical to the problem in Bug #11694: http://bugs.mysql.com/bug.php?id=11694
I did another test, and narrowed down the problem. It's not exactly a
regression; the test cases in Bug #11694 are still fixed correctly.
But it's a slightly different problem.
If the alias is for an expression instead of a plain column name in the
select-list, MySQL gives an error if you use the alias in an expression
in the ORDER BY clause.
I've logged a new bug: http://bugs.mysql.com/bug.php?id=22457
See that bug log for more detailed explanation, and steps to demonstrate
the bug.
Regards,
Bill K. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by Marcus |
last post: by
|
3 posts
views
Thread by Ed L. |
last post: by
|
1 post
views
Thread by Sandro Daehler |
last post: by
|
9 posts
views
Thread by Klaus Johannes Rusch |
last post: by
|
6 posts
views
Thread by Sile |
last post: by
|
7 posts
views
Thread by John |
last post: by
|
reply
views
Thread by Peter Nofelt |
last post: by
| | | | | | | | | | | | |