Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2002
    Posts
    15

    Question How to Sort the Numeric Data in a varchar data type field

    Hi

    the data in the database is 12,41,1,2,4,6,7,13,19,20. the data type of the field is varchar

    i want to sort that field by asc but the result is 1,10,11,12,13,14,15,16,17,18,19,2,20 like this it is coming.

    i want it like this 1,2,3,4,5,6.... how do i do that.

    Thanks in Advance

    Regards
    Gandhi

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can cast or convert the varchar as integer in your order by clause.

  3. #3
    Join Date
    Jul 2002
    Posts
    15
    Hi
    Thank you

    if the data is only numeric it is working fine. but in the same filed i am having text data also in different condintions i have to sort the text data the time the query is giving error

    Regards
    Gandhi

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Give me an example the text data that is mixed with the numeric data and how the text data would impact the sorting.

  5. #5
    Join Date
    Jul 2002
    Posts
    15
    Originally posted by rnealejr
    Give me an example the text data that is mixed with the numeric data and how the text data would impact the sorting.
    The Data is like this

    Dept_id Filter1_Desc

    PT01 1
    PT01 14
    PT01 2
    PT01 10
    DT01 Selangor
    DT01 KL

    my select Query is like this
    SELECT *
    FROM DIMENSION_MASTER
    WHERE (DEPT_ID = 'pt01')
    ORDER BY CAST(FILTER1_DESC AS Numeric)

    The above query is working fine

    SELECT *
    FROM DIMENSION_MASTER
    WHERE (DEPT_ID = 'Dt01')
    ORDER BY CAST(FILTER1_DESC AS Numeric)

    this time it is giving error i should not hard code the dept id if dept id is PT01 means one query and dept_Id is DT01 means another query the query should be common

    Thanks & Regards
    Gandhi

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Are there only 2 scenarios - pt01 (using numbers) and dt01 (using characters) ? And will the character scenario be sorted like a normal alpha sequence ?

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Since you want 1 generic query to handle both cases, how do you want the data displayed when you have both numbers and alphas in the same resultset ?

  8. #8
    Join Date
    Jul 2002
    Posts
    15
    Originally posted by rnealejr
    Since you want 1 generic query to handle both cases, how do you want the data displayed when you have both numbers and alphas in the same resultset ?
    presently no data is there in alpha numeric. if it is there also. it can be be any sequence no problem

  9. #9
    Join Date
    Jul 2002
    Posts
    15
    Originally posted by rnealejr
    Are there only 2 scenarios - pt01 (using numbers) and dt01 (using characters) ? And will the character scenario be sorted like a normal alpha sequence ?
    it is not only 2 scenarios somtimes PT01(can have alpha data) dt01(can have numeric data).yes charecter can sorted by alpha sequence. i want common quey.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Are you saying that if the data is all numeric sort, it by number - if it is mixed you don't care ?

    So if the following data is returned (from your example):

    Dept_id Filter1_Desc

    PT01 1
    PT01 14
    PT01 2
    PT01 10
    DT01 Selangor
    DT01 KL

    You don't care about the order of this resultset. If this is the case, how can the query be common ?

  11. #11
    Join Date
    Jul 2002
    Posts
    15
    Originally posted by rnealejr
    Are you saying that if the data is all numeric sort, it by number - if it is mixed you don't care ?

    So if the following data is returned (from your example):

    Dept_id Filter1_Desc

    PT01 1
    PT01 14
    PT01 2
    PT01 10
    DT01 Selangor
    DT01 KL

    You don't care about the order of this resultset. If this is the case, how can the query be common ?
    the data won't return in that way b'cos it is 2 different dept's one is 'PT02' and another one is DT01 i am filtering the query by dept id for that dept id the query has to return here 'dept_id' is one column and filter1_desc is another column i am seperating this one with '|'

    if i filter by PT01 the data has to sort in this way

    Dept_ID | Filter1_desc
    PT01 | 1
    PT02 | 2

    if i filter by DT01 the data has to sort in this way

    Dept_ID | Filter1_desc
    DT01 | KL
    DT02 | Selangor

    Thanks & Regards
    Gandhi

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    How did you want to generate this list - using a stored procedure ? How are you using this data, once the data is returned ?

  13. #13
    Join Date
    Jul 2002
    Posts
    15
    Originally posted by rnealejr
    How did you want to generate this list - using a stored procedure ? How are you using this data, once the data is returned ?
    i want this data to be return in a Recorset. i am using this data to populate the True DB Grid. IN grid it has to be shown in the Ascending Order.

    Thanks & Regards
    Gandhi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •