MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 28 2004 1:56pm
Subject:RE: Using function followed by "order by function_name(table.field_name)".....
View as plain text  
Let me quote a little bit from the docs 
(http://dev.mysql.com/doc/mysql/en/String_functions.html):

CHAR() interprets the arguments as integers and returns a string 
consisting of the characters given by the code values of those integers. 
NULL values are skipped. 

        mysql> SELECT CHAR(77,121,83,81,'76');
             -> 'MySQL'
        mysql> SELECT CHAR(77,77.3,'77.3');
             -> 'MMM'

That means that when it processes each of the fields in your resultset, it 
converts that string into some other string and is sorting by the results 
of that transformation. Definitely NOT what you wanted. Please, let's try 
again. Please, show me the results of just a plain "ORDER BY field_name" 
query (without the CHAR() function) and explain to me what is wrong...

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Scott Fletcher" <SFletcher@stripped> wrote on 07/28/2004 09:12:45 AM:

> The example of ORDER BY that didn?t solve the problem is ?
> 
> --snip--
> Debug Test (WM Account) 1
> ABC Company
> Riverknoll A C
> SDFONE'sdf
> SeaWater
> --snip--
> 
> which should be ?
> 
> --snip--
> ABC Company
> Debug Test (WM Account) 1
> Riverknoll A C
> SDFONE'sdf
> SeaWater
> --snip--
> 
> The word, ?Debug Test (WM Account) 1? came before the ?ABC Company? 
> which should be the other way around?.  This show that the ?ORDER BY
> CHAR(??)? doesn?t work.  I just don?t see how to use a function that
> would sort the char in ascending order properly?
> 
> Thanks,
>  FletchSOD
> 
> 
> 
> From: SGreen@stripped [mailto:SGreen@stripped] 
> Sent: Wednesday, July 28, 2004 7:58 AM
> To: Scott Fletcher
> Cc: mysql@stripped
> Subject: Re: Using function followed by "order by 
> function_name(table.field_name)".....
> 
> 
> I hope you have already tried just plain-old "ORDER BY field_name" 
> in your query.  What is wrong with the default order presented? Can 
> you show an example of how the ORDER BY did not solve your problem 
> along with an example of what it should have been for your situation? 
> 
> Thanks in advance, 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 
> "Scott Fletcher" <SFletcher@stripped> wrote on 07/27/2004 05:29:05 PM:
> 
> > I'm having a little bit of a trouble with the use of the SQL 
function...
> > What I have here is a webpage that show the row of data, the web user
> > get to click on the field to sort the row by the field name.  So, when 
I
> > use "order by char(field_name)", the data doesn't turned out right in
> > alpha-numeric in ascending order...
> > 
> > 
> > 
> > --snip--
> > 
> > Debug Test (WM Account) 1
> > 
> > ABC Company
> > 
> > Riverknoll A C
> > 
> > SDFONE'sdf
> > 
> > SeaWater
> > 
> > --snip--
> > 
> > 
> > 
> > I would like the first few letter of each row be in correct
> > alpha-numeric order regardless of what character is there in the data,
> > like (, ), ', ?, etc...
> > 
> > 
> > 
> > Thanks,
> > 
> >  FletchSOD
> > 
Thread
Using function followed by "order by function_name(table.field_name)".....Scott Fletcher27 Jul
  • Re: Using function followed by "order by function_name(table.field_name)".....SGreen28 Jul
RE: Using function followed by "order by function_name(table.field_name)".....SGreen28 Jul
RE: Using function followed by "order by function_name(table.field_name)".....SGreen28 Jul