List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 11 2003 4:45pm
Subject:RE: SELECT * FROM foo ORDER BY bar DESC NAT
View as plain text  
At 17:36 +0100 4/11/03, Russell Packer wrote:
>  > -----Original Message-----
>>  From: Fred van Engen [mailto:fred.van.engen@stripped]
>
>>  Subject: Re: SELECT * FROM foo ORDER BY bar DESC NAT
>>
>>
>>  Hi,
>>
>>  On Fri, Apr 11, 2003 at 05:18:19PM +0100, Russell Packer wrote:
>>  > I've searched - can't find a thing.
>>  >
>>  > Is there any way to retrieve string data in natural sort order
>>  >
>>  > eg.
>>  >
>>  > 1
>>  > 2
>>  > 10
>>  > 11
>>  > 20
>>  >
>>
>>  Just make it a numeric column.
>>
>>
>>  > Rather than
>>  >
>>  > 1
>>  > 10
>>  > 11
>>  > 2
>>  > 20
>>  >
>>
>>  It appears to be a text column now.
>>
>>
>>  If it MUST be a text column for some reason (why?), you can make the
>>  values numeric by adding 0, i.e. ORDER BY bar+0. This will be much
>>  slower than using an indexed numeric field. Also, sorting a list of
>>  text values like below won't work in either case:
>>
>>  1 abc
>>  2 def
>>  10 ghi
>>
>>  Good luck if that's what you need ;)
>>
>>
>>  Regards,
>>
>>  Fred.
>>
>
>Its a text column as I am storing IP addresses. While I could split 
>the number into numeric quads, this is too large a workload.
>
>I'll take it then that that is no way in MySQL to perform a natural 
>sort then. Ah well, I'll just use the PHP natsort(). Dang. :-(

You've described your problem two ways.  Initially you gave examples
with simple numbers.  Now you say you're storing IP numbers, and it sounds
as though you're storing dotted quads represented as strings.

So thus far you have not really described what you're *actually* storing,
or what "natural" order you expect. This makes it difficult for people to
help you.

If you *are* storing dotted quads, perhaps you could use
ORDER BY INET_ATON(bar) DESC.
Thread
SELECT * FROM foo ORDER BY bar DESC NATRussell Packer11 Apr
  • Re: SELECT * FROM foo ORDER BY bar DESC NATFred van Engen11 Apr
RE: SELECT * FROM foo ORDER BY bar DESC NATRussell Packer11 Apr
  • Re: SELECT * FROM foo ORDER BY bar DESC NATFred van Engen11 Apr
    • [mysql] Linking distributed MySQL servers...Ali Naqi11 Apr
      • Re: [mysql] Linking distributed MySQL servers...Jeremy Zawodny18 Apr
  • RE: SELECT * FROM foo ORDER BY bar DESC NATPaul DuBois11 Apr
RE: SELECT * FROM foo ORDER BY bar DESC NATRussell Packer11 Apr