Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Sorting Problem

  1. #1
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7

    Unanswered: Sorting Problem

    In my table there is a field with type varchar and this field contains both numeric and alphanumeric values. I need to sort the data such that numeric values should be sorted based on numeric value (1,2,3,4,5,6,7,8,9,10,11...) and alphanemeric values should be order by aplhabeticas (a,b,c,d...). Numeric values should appear first.

    My data is like this
    1.10, 1.20, 10.10, 10.20, 2.10, 2.20, R1.10, R1.20, R10.10, R10.20, R2.10, R2.20,

    I want like this
    1.10, 1.20, 2.10, 2.20, 10.10, 10.20, R1.10, R1.20, R2.10, R2.20, R10.10, R10.20

    Thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Sorting Problem

    Your problem is that your table isn't normalized as it contains three values in one field:

    a) numeric / alphanumeric indication
    b) major number
    c) minor number

    You want a sorting like
    ORDER BY a ASC, b ASC, c ASC

    which requires the need of a specification of your three parts out of your one field:

    a: case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end

    c: case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end

    b: substring(<YourField>, len(a)+1, len(<YourField>)-len(a)-len(c)-case when len(c)=0 then 0 else 1 end)
    whereby the length of a is zero if there is not alphanumeric indication.

    So, your query will be:

    SELECT * FROM <YourTable> ORDER BY
    case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end ASC,
    substring(<YourField>, len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)+1, len(<YourField>)-len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)-len(case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end)-case when len(case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end)=0 then 0 else 1 end) ASC,
    case when charindex( '.',<YourField>)=0 then '' else substring(<YourField>,charindex( '.',<YourField>)+1, len(<YourField>)) end ASC

    Cheers!
    Last edited by DoktorBlue; 09-01-03 at 04:51.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Sorting Problem

    I forgot a detail:

    b) and c) have to be converted to a number. So, in the order by clause, replace a ASC, b ASC, c ASC by
    ORDER BY a ASC, cast(b as int) ASC, cast(c as int) ASC

    which results in this query:

    SELECT * FROM <YourTable> ORDER BY
    case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end ASC,
    cast(substring(<YourField>, len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)+1, len(<YourField> )-len(case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end)-len(case when charindex( '.',<YourField> )=0 then '' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end)-case when len(case when charindex( '.',<YourField> )=0 then '' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end)=0 then 0 else 1 end) as INT) ASC,
    cast(case when charindex( '.',<YourField> )=0 then '0' else substring(<YourField>,charindex( '.',<YourField> )+1, len(<YourField> )) end as INT) ASC
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    It is working fine for the example data given by me in my first query.
    But my data have R.R/15.10, UG/18.20 for which it is unable to convert to INT. No other posiblilites of data other than I mentioned in my column.

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It is being the same problem, but you will have to change the definition of your first portion a), and you will have to take into account that you may have two points during determination of c).

    a: case when charindex('/',<YourField>)=0 then case when left(<YourField>,1) between '0' and '9' then '' else left(<YourField>,1) end else substring(<YourField>,1,charindex('/',<YourField>))

    c (replace a) where occuring: case when charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>)) )=0 then '' else substring(<YourField>,charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>) )+1, len(<YourField> )) end

    b (keeps the same, replace a & c where occuring): substring(<YourField>, len(a)+1, len(<YourField> )-len(a)-len(c)-case when len(c)=0 then 0 else 1 end)


    Use a) as described within b) and c), but change a) when writing your ORDER BY at two positions:
    case when charindex('/',<YourField>)=0 then case when left(<YourField>,1) between '0' and '9' then '0' else left(<YourField>,1) end else substring(<YourField>,1,charindex('/',<YourField>)-1)

    Also use c) within b) as desribed above, but change it in this way, when writing your ORDER BY:

    case when charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>)) )=0 then '0' else substring(<YourField>,charindex( '.',substring(<YourField>, len(a)+1, len(<YourField>) )+1, len(<YourField> )) end
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    Hi DoktorBlue,

    Thank You very much for your great help. My problem is solved.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is a lot more typing than I would want to do.

    If the numeric portion of your values is always 99 or less, you could use
    ORDER BY right('0000000000' + [YourField], 10) asc

    If this is not the case, and "Numeric values should appear first.", then try
    ORDER BY Isnumeric([YourField]) desc, right('0000000000' + [YourField], 10) asc

    blindman

  8. #8
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    Hi blindman (Guru)

    Seems to be simple, but my purpose is not solved. Please see attached excel sheet (zip file) so that you will have idea on how the data is generated
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Angry Wrong thread?!

    blindman, your posting must be mistaken from another thread. Otherwise, you are exposing yourself incapable understanding the problem and the proposed solutions. Why didn't you not even download Mohan's description? Contribute to the thread, or be quiet!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I inspected your list, and I found three values, that would not be handled correctly by my solution:

    479.20 & 478.20
    CH162
    72.20a

    Did you solved this already?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't bust a vein Dr. Blue! Forget to take your Prozac today?

    Something is interfering with your concentration and your ability to link temporal events, or you'd have noticed that mohan1976 didn't post the attachment until after my post. My solution worked fine with the data that had been posted.

    "you are exposing yourself incapable understanding"?

    You're starting to get funny again...


    Mohan,
    Your spreadsheet contains more data, but it doesn't indicate the desired sort order. What do you want to do with data like "479.20 & 478.20", "CH162", and "72.20a"?

    Honestly, if you only have to do this once and the Blue Doctor's code works, and you're not going to have to read the code again a year from now and try to figure out what it is doing, then go with Blue's solution.
    Otherwise, post some more information on how you want to handle data exceptions, and I'd be curious to know what the data reprents as well.

    blindman

  12. #12
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    Hi,

    479.20 & 478.20 -- not handeled as it is very very rare case and not bothering about it

    72.20a is a bad data -- changed to 72.20
    CH162 -- wrong entry, so deleted

    Sorry for provided the wrong data to the right persons

    Basically my data starts with any interger or RR/ or R.R/ or UG/ (Major part) and each values ends with either .10 or .20 or .30 (Minor part) only. If Major part starts with integer then the total value will be integer value only.

    One report needs order by Minor part followed by Major part in ascending order (here if major part contains both integer and alphanumeric values then order by integer values first and then the alphanumeric values to be sorted)

    In another report order by Major part (here also if major part contains both integer and alphanumeric values then order by integer values first and then the alphanumeric values to be sorted) followed by Minor part in ascending order

    Thanks

  13. #13
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    If there is any way to do it, I strongly recomment to normalize your table, splitting your code up into three parts (note that what you call Major consists of two parts, a numeric and eventually an alphanumeric portion).

    It prevents you from writing complex order clauses, which are indeed difficult to maintain. Therefore, I suggest to put my functional specs as comments into that query.

    If you have to use these splitting several times in different queries, consider to use a view, wherein you normalize your field as specified. You can subsequently use the parts within you (easy) order clause. This is actually a variation to my first comment.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  14. #14
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    Finally I came to conclution to use

    1)To sort by my Minor Part first
    SELECT * FROM <Your Table> ORDER BY
    substring(<Your Field>,len(<Your Field>)-1,len(<Your Field>)),
    Isnumeric(<Your Field>) desc,
    substring(<Your Field>,charindex('/',<Your Field>)-1,1) desc,
    right('0000000000' + <Your Field>, 10)


    2)To sort by whole filed
    SELECT * FROM <Your Table> ORDER BY
    substring(<Your Field>,len(<Your Field>)-1,len(<Your Field>)),
    Isnumeric(<Your Field>) desc,
    substring(<Your Field>,charindex('/',<Your Field>)-1,1) desc,
    right('0000000000' + <Your Field>, 10)


    The above two are solving my purpose as the data 479.20 & 478.20
    is made as two seperate values as 479.20 and 478.20

    Thanks

  15. #15
    Join Date
    Sep 2003
    Location
    Hyderabad, India
    Posts
    7
    Sorry

    for my second, I forgottent to delete the second line in the query while posting here in previous reply. It should be

    2)To sort by whole filed
    SELECT * FROM <Your Table> ORDER BY
    Isnumeric(<Your Field> ) desc,
    substring(<Your Field>,charindex('/',<Your Field> )-1,1) desc,
    right('0000000000' + <Your Field>, 10)

Posting Permissions

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