Hi,

I'm trying to do a "SELECT max(storage_name) from table", but MySQL
confuses Rack9 to be the greatest value in stead of Rack 10.

Does this have to do with a certain column type or collation?

This is my table:

CREATE TABLE `table` (
`storage_id` mediumint(9) unsigned NOT NULL auto_increment COMMENT
'PK to identify a storage',
`storage_name` text NOT NULL COMMENT 'Name of the storage',
`storage_left` mediumint(9) unsigned NOT NULL default '0' COMMENT
'Id of the left storage',
`storage_right` mediumint(9) unsigned NOT NULL default '0' COMMENT
'Id of the right storage',
PRIMARY KEY (`storage_id`),
UNIQUE KEY `storage_left` (`storage_left`,`storage_right`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;


INSERT INTO `table` (`storage_id`, `storage_name`, `storage_left`,
`storage_right`) VALUES
(1, 'Storage', 1, 46),
(2, 'Freezer A', 2, 37),
(3, 'Shelf 1', 3, 36),
(4, 'Rack 1', 4, 5),
(5, 'Freezer B', 38, 39),
(6, 'Freezer C', 40, 41),
(7, 'Freezer D', 42, 43),
(8, 'Freezer E', 44, 45),
(9, 'Rack 2', 6, 7),
(10, 'Rack 3', 8, 9),
(11, 'Rack 4', 10, 11),
(12, 'Rack 5', 12, 13),
(13, 'Rack 6', 14, 15),
(14, 'Rack 7', 16, 17),
(15, 'Rack 8', 18, 19),
(16, 'Rack 9', 20, 21),
(17, 'Rack 10', 34, 35),
(18, 'Rack 10', 32, 33),
(19, 'Rack 10', 30, 31),
(20, 'Rack 10', 28, 29),
(21, 'Rack 10', 26, 27),
(22, 'Rack 10', 24, 25),
(23, 'Rack 10', 22, 23);

This table is used to store hierarchical data, using MPTT.

On Wed, 30 Jan 2008 20:40:13 +0100, <pieter.thoma@gmail.com> wrote:

> Hi,
>
> I'm trying to do a "SELECT max(storage_name) from table", but MySQL
> confuses Rack9 to be the greatest value in stead of Rack 10.
>
> Does this have to do with a certain column type or collation?

It is a string, and hence will be sorted by string values, thus:

Rack 1
Rack 10
Rack 11
Rack 1000000
Rack 2
Rack 3
Rack 31
Rack 4

.... etc...
--
Rik Wasmus

Is there a hack or workarround? Apart from storing Rack in a column
and the Number in another.

pieter.thoma@gmail.com wrote:
> Is there a hack or workarround? Apart from storing Rack in a column
> and the Number in another.

You could format them like:

Rack 1
Rack 2
Rack 3
Rack 4
Rack 5
Rack 6
Rack 7
Rack 8
Rack 9
Rack 10
Rack 11
Rack 12
Rack 13
Rack 14
Rack 15
Rack 16

Paul Lautman wrote:
> pieter.thoma@gmail.com wrote:
>> Is there a hack or workarround? Apart from storing Rack in a column
>> and the Number in another.
>
> You could format them like:
>
> Rack 1
> Rack 2
> Rack 3
> Rack 4
> Rack 5
> Rack 6
> Rack 7
> Rack 8
> Rack 9
> Rack 10
> Rack 11
> Rack 12
> Rack 13
> Rack 14
> Rack 15
> Rack 16

You need to view my previous post in a fixed width font to see what I mean!

Hi,

I can not change my display to see what you mean. When I look to the
source of your comment you have:

Rack &nbsp;1
Rack 10

Does this has to do with what you suggested?

Cheers

Pieter

On Wed, 30 Jan 2008 13:03:22 -0800 (PST),
pieter.thoma@gmail.com wrote:

>Hi,
>
>I can not change my display to see what you mean. When I look to the
>source of your comment you have:
>
>Rack &nbsp;1
>Rack 10
>
>Does this has to do with what you suggested?
>
>Cheers
>
>Pieter

'Rack' space space '1'
'Rack' space '10'
etc.

or
'Rack001'
'Rack010'
etc.

It's a string, so it sorts like a string.
--
( Kees
)
c[_] Other than telling us how to live, think, marry,
pray, vote, invest, educate our children, and now,
die, I think the Republicans have done a fine job
of getting government out of our personal lives. (#213)