ORDER BY upper AND by right() fails

[ppowell]

I am having to do a simple query where I have to order by a column in alphabetical order AND by ordinal order at the exact same time.

Following are the results of my actions:

mysql> select * from placement order by upper(placement_name) asc, right(placement_name, 2);
+----+-----------------------------+------------------+---------------------+
| id | placement_name | unique_key | record_entered |
+----+-----------------------------+------------------+---------------------+
| 6 | http://valsignalandet.com | oIupXWqil4wmqvbh | 2004-03-22 13:12:14 |
| 2 | Placement #1 | 0HWo8GOxRsHRoYDk | 2004-03-09 12:16:04 |
| 4 | Placement #10 | KKU29jFq8ALEwN7T | 2004-03-09 12:16:22 |
| 3 | Placement #2 | zKVdvPfcM2tE0fyb | 2004-03-09 12:16:14 |
+----+-----------------------------+------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from placement order by right(placement_name, 2), upper(placement_name) asc;
+----+-----------------------------+------------------+---------------------+
| id | placement_name | unique_key | record_entered |
+----+-----------------------------+------------------+---------------------+
| 2 | Placement #1 | 0HWo8GOxRsHRoYDk | 2004-03-09 12:16:04 |
| 3 | Placement #2 | zKVdvPfcM2tE0fyb | 2004-03-09 12:16:14 |
| 4 | Placement #10 | KKU29jFq8ALEwN7T | 2004-03-09 12:16:22 |
| 6 | http://valsignalandet.com | oIupXWqil4wmqvbh | 2004-03-22 13:12:14 |
+----+-----------------------------+------------------+---------------------+
4 rows in set (0.00 sec)

Both are incorrect queries. I need it to be in the order of 'http://', 'Placement #1', 'Placement #2' and 'Placement #10'. Combining the order by criteria produces one or the other result but but the combination of both.

I am using MySQL 3.23.41 and 4.0.10 on different servers both having Red Hat Linux 7.3.

Thanx
Phil

[mrhappiness]

try

ORDER BY
UPPER(placement_name) ASC,
RIGHT(placement_name, 2) + 0

[ppowell]

Results:

mysql> select * from placement order by upper(placement_name) asc, right(placement_name, 2) + 0;
+----+-----------------------------+------------------+---------------------+
| id | placement_name | unique_key | record_entered |
+----+-----------------------------+------------------+---------------------+
| 6 | http://valsignalandet.com | oIupXWqil4wmqvbh | 2004-03-22 13:12:14 |
| 2 | Placement #1 | 0HWo8GOxRsHRoYDk | 2004-03-09 12:16:04 |
| 4 | Placement #10 | KKU29jFq8ALEwN7T | 2004-03-09 12:16:22 |
| 3 | Placement #2 | zKVdvPfcM2tE0fyb | 2004-03-09 12:16:14 |
+----+-----------------------------+------------------+---------------------+
4 rows in set (0.00 sec)

Sorry but that's still not right. It has to be "http://', 'Placement #1', 'Placement #2', 'Placement #10'

🙁

Phil

[mrhappiness]

with your mysql 4 you can do CAST(RIGHT( ...) AS int) i guess

with mysql 3 perhaps LPAD(RIGHT(...), 2, '0') will help

[ppowell]

Sorry, unless I did the syntax wrong, this was the result using MySQL 3.23.41:

mysql> select * from placement order by lpad(right(upper(placement_name), 2), '0');
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

and using MySQL 4.0.10:

mysql> select * from placement order by cast(right(upper(placement_name), 2) as int i;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'int i' at line 1

Phil

[mrhappiness]

CAST( RIGHT(UPPER(placement_name), 2) AS int)

LPAD (RIGHT(UPPER(placement_name), 2), 2, '0')

try this

[ppowell]

Ok I'm sorry if I am still doing this wrong, but..

mysql> select * from placement ORDER BY LPAD (RIGHT(UPPER(placement_name), 2), 2, '0');
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(RIGHT(UPPER(placement_name), 2), 2, '0')' at line 1

mysql> select * from placement order by CAST( RIGHT(UPPER(placement_name), 2) AS int);
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 1

Phil

[mrhappiness]

http://www.mysql.com/doc/en/Cast_Functions.html

try SIGNED or UNSIGNED

[ppowell]

Sorry, can't use either one. I am using MySQL 3.23.41 on one machine and MySQL 4.0.10 on the other:

CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.

I am about to give up on this whole thing and tell the client it's architecturally impossible to sort by both alphanumeric order and by ordinal order in MySQL that is available via client unless they upgrade.

Phil

[Sxooter]

Will it work if you put the part to order by in the select list?

select *, right(col,2) as rc, upper(col2) as uc from table order by uc asc, rc asc;

???

[ppowell]

Well, it produces a query, but sadly, also in the wrong order:

mysql> select *, right(placement_name, 2) as rc, upper(placement_name) as uc from placement order by uc asc, rc asc;
+----+-----------------------------+------------------+---------------------+----+-----------------------------+
| id | placement_name | unique_key | record_entered | rc | uc |
+----+-----------------------------+------------------+---------------------+----+-----------------------------+
| 6 | http://valsignalandet.com| oIupXWqil4wmqvbh | 2004-03-22 13:12:14 | ov | HTTP://VALSIGNALANDET.COM |
| 2 | Placement #1 | 0HWo8GOxRsHRoYDk | 2004-03-09 12:16:04 | #1 | PLACEMENT #1 |
| 4 | Placement #10 | KKU29jFq8ALEwN7T | 2004-03-09 12:16:22 | 10 | PLACEMENT #10 |
| 3 | Placement #2 | zKVdvPfcM2tE0fyb | 2004-03-09 12:16:14 | #2 | PLACEMENT #2 |
+----+-----------------------------+------------------+---------------------+----+-----------------------------+
4 rows in set (0.00 sec)

Geez I didn't think this would be so hard to do a simple query.

Phil

[Sxooter]

Can you cast the right() function to an int in the select clause?

Man, if this was Postgresql I'd be done by now... 🙁

[ppowell]

No.. CAST() is not supported by MySQL 3.23.41 nor by MySQL 4.0.10 the versions we have here..

Phil

[Sxooter]

Is there some kind of regex matching for a substring() type function in MySQL? if not, then I'm stuck. I can think of eight ways to do this in most databases, but in MySQL, not a one if that doesn't work.

[csn]

I think you're better off changing your table schema - "http://valsignalandet.com" and "Placement #1" are the same kind of data and belong in the same column?

That sort doesn't work because with the first order clause - upper(placement_name) - "PLACEMENT #10" comes after "PLACEMENT #1", and the second order clause - right(placement_name, 2) - doesn't matter in this case (the first takes precedence). Only if upper(placement_name) produced the same string, would right(placement_name, 2) order them as you want.

[csn]

Hey Sxooter-

With PG, would it be possible to create an index on the last integer in a field (like placement_name) and sort by it? right(placement_name, 2) wouldn't work because, well, not all numbers have two places 😉.

[ppowell]

I'm sorry but your answer makes no sense to me. Change schema? I can't do that because it's pulling solely from one dynamically named column from a dynamically named table.

It's not just "placement", it's up to five different tables, all having the same datatype and naming convention:

image.image_name
event.event_name
placement.placement_name
etc.

And the "${section}_table" column can literally have anything in it, there is no rule per se for content, just that it has to have content and that's all. So the placement_name column could have a URL or "Placement #1" or anything at all.

Phil

[Sxooter]

Originally posted by csn
Hey Sxooter-

With PG, would it be possible to create an index on the last integer in a field (like placement_name) and sort by it? right(placement_name, 2) wouldn't work because, well, not all numbers have two places 😉.

You could with substring / regex pattern matching, I'd think. You do know that pgsql has a

substring("string","regex")

version of substring, right? It's tres cool for stuff like this.

[ppowell]

Well, I did the "non-technical" approach that would appeal to Joe Normal Guy. I just told the client that the tools provided do not allow for a comprehensive alphanumeric/ordinal sort, so I told them it's alphanumeric or we have to do a site redesign to enforce a naming convention on all user-added input.

Phil

[csn]

Of course I knew 😉. It should be simple to extract the last integer - a regexp like:

.*([0-9]+)$

But how would you specify in your query to sort by that index?