locked
Order By SQL Statement Not Working RRS feed

  • Question

  • User-1362966093 posted

    Hi All.

    select * from caraccount order by carno ASC;

    My products that are displaying display as....

    1, 11, 12, 2, 21, 22.... Instead of , 1, 2, 3, 4, ....

    This is happening because my carno field is a string, and it is sorting alphabetically, whereas i want to treat it as a number, and sort it numerically.

    If i want to order by the carno field numerically, i need to convert the numeric portion of the string to a number. Use the cast statement for this:

    select * from caraccount order by CInt(carno) ASC;

    It is giving error Please can you rectify me.

    Thanks.

    Zahyea.

    Saturday, October 20, 2007 8:45 AM

Answers

  • User-158764254 posted

    This thread has now been moved from the MySql forum to the MS-Access forum.

    As i'm sure you now realize, the forum you post to can have a great deal of influence on the type of help you will receive.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2007 10:10 PM

All replies

  • User-158764254 posted

    try using Cast

    select * from caraccount order by Cast(carno As integer) ASC;
    
    
     
    Saturday, October 20, 2007 9:38 AM
  • User-1362966093 posted

    Dear Sir.

    select * from caraccount order by Cast(carno As integer) ASC

    This is giving error : IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

    I am using C# can you assit why error is coming.

    Thanks for your reply.

    Zahyea

    Saturday, October 20, 2007 9:44 AM
  • User-158764254 posted

    Sql queries are not written in C#.

    They need to use T-Sql syntax.

    I dont have a MySql setup handy to test against, but it looks like it may use the keyword SIGNED instead of integer.

    select * from caraccount order by Cast(carno As SIGNED) ASC;

     

    Saturday, October 20, 2007 9:55 AM
  • User120819611 posted

    Yes, it could work both with SIGNED and UNSINGED.

    You can try this,

    select * from caraccount order by Cast(carno As UNSINGED) ASC;

    or 

    or select * from caraccount order by Cast(carno As SIGNED) ASC

     

    cheers,

    CLIPER 

    Sunday, October 21, 2007 11:05 PM
  • User-1362966093 posted

    Dear Sir.

     Have a nice day.

    select * from caraccount order by Cast(carno As UNSINGED) ASC;

    or 

    or select * from caraccount order by Cast(carno As SIGNED) ASC

    Both are giving error. I changed the code and used like this --- select Cast(carno As SIGNED) from caraccount order by carno ASC; even it is giving error.

     I am using C#. and i never use key word "As" in C#. Please can you assist where i am wrong.

    Thanks.

    Zahyea

    Monday, October 22, 2007 6:52 PM
  • User1187105292 posted

     Zahyea,

    The query you are executing is a sql statement and has to be written in sql.  As far as the statement itself is concerned and the database that will run it, c# is irrelevant.

    C# is used to ask the database to run the sql statement and to process the results that come back.

    Some of the other posters have been giving you advice based upon SqlServer's brand of Sql.

    You need advice based on MySql's brand of Sql.  At least, I assume you do, because this was posted in the MySql thread.

    I do not work with MySql.  What I can tell you is that MySql must have a function that converts a string-type variable to a numeric-type variable.  You need to crack the MySql book and find that function.  It can't be all that hard to find.  When you find that function, you need to call it in the order by clause and supply  carno as the string input parameter value.

    Then, when you think you have the correct statement, you need to log into MySql and test it inside MySql.  Once the query is proven to work, then you can use c# to call it.


     

    Monday, October 22, 2007 7:28 PM
  • User-158764254 posted

    The Cast function actually is a documented MySql function that claims to use Standard SQL Syntax

    http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html

    Something is definately amiss though. There are suggestions in that link that encouraging an implicit converstion to a numeric value may be more successful than an explicit cast.  Not sure why this might be, but, something like this may have more success:

    select * from caraccount order by (carno * 1) ASC;
    

     

     

    Monday, October 22, 2007 8:17 PM
  • User1187105292 posted

    Have you verified that all of the data in the field is, in fact, capable of being turned into an integer?  Any chance of invalid data? 

    Monday, October 22, 2007 8:40 PM
  • User-1362966093 posted

    Dear Sir.

     Have a nice day.

     I am testing all again and again but all giving error .

    In My table one field is Text, and i wanted in my asp.net datagrid result in Number(integer).

    I could not do this for this last week. I check datagrid Formatting Expression also but fail.

    Thanks.

    Monday, October 22, 2007 8:58 PM
  • User1187105292 posted
    please post what this statement returns:
    select distinct 
    replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(carno,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')
    from caraccount order by carno ASC;

    The '' you see is actually a single quote followed by a single quote, not a double quote. 

    Basically, I am replacing all digits with an empty space.  The query should return one row with a null value.

    If it returns anything else, the data is not all integers.

     

    Monday, October 22, 2007 9:22 PM
  • User-1362966093 posted

    Dear Sir.

    Have a nice day.

    It put your given code in my page but it is saying Undefined Function 'replace' in expression

    Actually

    In my MS access Table One field i create carno (Text). If i make this field in database Carno(Number) then my problem solve. But in the Carno Field i am entering data like this

    car(1), car(4), car(88), car(2), car(6).

    So i must i have to keep carno field Text.

    But in the Datagrid i have to show Carno in sequence, It is possible if i convert the Text field in number or integer.

    Now please can you guide me.

    Thank you very much for writing .

    Zahyea.

    Monday, October 22, 2007 9:41 PM
  • User1187105292 posted

    In my MS access Table

    You are using MS Access for your database?  This is the MySql forum, for the MySql database product.

    If you are using MS Access, you should be posting in the Access forum, http://forums.asp.net/55.aspx

    Basically, you have been getting advice for the wrong product because you asked in the wrong forum.

     

    One field i create carno (Text). If i make this field in database Carno(Number) then my problem solve. But in the Carno Field i am entering data like this

    car(1), car(4), car(88), car(2), car(6).

    So i must i have to keep carno field Text.

    But in the Datagrid i have to show Carno in sequence, It is possible if i convert the Text field in number or integer.

    You cannot convert a value like car(1) to an integer.  "car" is not a valid integer value.

    In theory, you could write code to read the value, test for whether the value between the ( ) marks is a valid number, and then format it with leading zeroes like this:

    car(0000001)
    car(0000004)
    car(0000088)

    But I don't program in Access, so I can't help you.

    You will get more help if you post your question in the Access forum, and take care to clarify what you need up front.

    Monday, October 22, 2007 9:59 PM
  • User-1362966093 posted

    I am sorry.

    But you helped me.

    Thanks.

    Monday, October 22, 2007 10:06 PM
  • User-158764254 posted

    This thread has now been moved from the MySql forum to the MS-Access forum.

    As i'm sure you now realize, the forum you post to can have a great deal of influence on the type of help you will receive.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 22, 2007 10:10 PM