Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    9

    Unanswered: Sorting Columns Question

    hi all

    an Interesting question

    I have a column which stores a versin number in this format

    1.5.5.19
    1.5.5.9
    ...
    ...

    I want to be able to sort this text column in an ascending order. Unfortunately it gives me 1.5.5.19 followed by 1.5.5.9 which is not the case.

    Please let me know ASAP

    Krish+
    v

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are they always 4 position version numbers?
    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.

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    9
    Yes there r always 4 position numbers

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I liked my answer there, athough I can take a stab at answering it here too.

    The problem lies in the fact that version numbes are typically strings of pairs of numbers, each one of which has to be interpreted as an integer. Each pair is atomic, it can't be split. Each pair also depends on the preceeding (further left) pairs.

    The problem has been debated for ages in the Unix community. The numbering scheme is easy for humans to use, but a challenge for machines to manipulte without specific routines that understand the notation.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if they are

    Code:
    USE Northwind
    
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    INSERT INTO myTable99(Col1)
    SELECT '1.5.5.19'  UNION ALL
    SELECT '1.5.5.9'   UNION ALL
    SELECT '1.5.15.19' UNION ALL
    SELECT '1.25.5.9'
    GO
    
      SELECT * 
        FROM myTable99 
    ORDER BY 
    	 CONVERT(int,PARSENAME(Col1,4))
    	,CONVERT(int,PARSENAME(Col1,3))
    	,CONVERT(int,PARSENAME(Col1,2))
    	,CONVERT(int,PARSENAME(Col1,1))
    GO
    
    DROP TABLE myTable99
    GO
    As stolen from:

    Damain's Article
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is deviant! I LIKE it!

    -PatP

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    9
    Does it work for MySQL too or is there any PARSENAME equivalent there?

    Kris

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I would try:

    http://dev.mysql.com/doc/

    Or

    http://www.dbforums.com/f5

    Did you expect to find a big mac at a 4 star restaurant?

    If they don't have it's gonna be a matter of parsing and build functions...do they have functions yet?

    All kidding aside...good luck....
    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.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK,

    Look at

    http://dev.mysql.com/doc/mysql/en/String_functions.html

    And The MAKE_SET function...looks close...

    But you should keep that online manual open all the time
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This just in...mySQL supports UNION in latest release!

    http://dev.mysql.com/doc/mysql/en/UNION.html
    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.

Posting Permissions

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