Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Ordering data separated by commas

    Hey everyone. I've never posted on this site before so if I miss anything, please let me know.

    I need to order the following data

    18
    14,15
    13.2
    14.2
    15,16
    15
    0
    14.1
    12
    6
    15,16,18
    11
    16
    15,17
    13
    14
    14,15.2
    17

    into this order:

    0
    6
    11
    12
    13
    13.2
    14
    14.1
    14.2
    14,15
    14,15.2
    15
    15,16
    15,16,18
    15,17
    16
    17
    18

    Because the data contains commas, I've had to change the data type to varchar. I've seen several instances of PATINDEX used however I have not ran across any of them that would put the above in the order in which I need. Btw, I have about 20 columns with this type of data and I eventually plan to use the results of each column to populate dropdownlists . Thank you all in advance.
    Last edited by eagle1; 01-01-08 at 13:32.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rather than sorting this data, perhaps we can suggest that you not store it that way?

    that would solve not only this problem, but other problems that you haven't yet encountered

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    2
    I wish I could say we can do that however this data doesn't belong to us as it belongs to a federal agency. The data that was posted earlier is DNA data haplotypes. Anyhow, any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, so import the data, and transform it

    E=extract, T=transform, L=load

    transform that pesky varchar into a separate one-to-many table, thus removing the commas, and into either two integer columns (if 14.12 comes after 14.2) or a single decimal column (if 14.12 comes before 14.2), thus removing the periods
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Seems like an operation better suited for a good ole shell script, perl or awk.

    Haplo = half ? right ?
    Diplo = both right ?

    I kinda remember my HS biology and associated latin terms.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by PMASchmed
    Seems like an operation better suited for a good ole shell script, perl or awk.

    Haplo = half ? right ?
    Diplo = both right ?

    I kinda remember my HS biology and associated latin terms.
    Nah

    Code:
    CREATE TABLE #myTemp99(Col1 varchar(20))
    GO
    
    INSERT INTO #myTemp99(Col1)
    SELECT '18' 		UNION ALL
    SELECT '14,15' 		UNION ALL
    SELECT '13.2' 		UNION ALL
    SELECT '14.2' 		UNION ALL
    SELECT '15,16' 		UNION ALL
    SELECT '15' 		UNION ALL
    SELECT '0' 		UNION ALL
    SELECT '14.1' 		UNION ALL
    SELECT '12' 		UNION ALL
    SELECT '6' 		UNION ALL
    SELECT '15,16,18' 	UNION ALL
    SELECT '11' 		UNION ALL
    SELECT '16' 		UNION ALL
    SELECT '15,17' 		UNION ALL
    SELECT '13' 		UNION ALL
    SELECT '14' 		UNION ALL
    SELECT '14,15.2' 	UNION ALL
    SELECT '17'
    GO
    
    SELECT * FROM #myTemp99
    ORDER BY REPLICATE(0,10)+CONVERT(int,SUBSTRING(Col1,1,2))
    GO
    
    DROP TABLE #myTemp99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    wait, that didn't work
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice try, todd, but your "solution" produces the following (partial) "sorted" results --

    14.2
    14.1
    14,15
    14
    14,15.2

    but thanks for playing, there will be a consolation prize as you leave...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, i see we crossed posts, i was busy testing while you were retracting...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    who's todd?

    In any case it's like you need vertical and horizontal ordering

    I think Rudy's paid for some horizontal ordering
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    nice try, todd, but your "solution" produces the following (partial) "sorted" results --

    14.2
    14.1
    14,15
    14
    14,15.2

    but thanks for playing, there will be a consolation prize as you leave...

    Did he say he worked for the goverment?

    Then it's fine
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "todd" is a canonical term of endearment

    what's "horizontal ordering"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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