Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    21

    Unanswered: Help with padding version numbers

    Hello,
    I have a query which get's the latest version of a software application in a software table. But the problem I'm running in to is when something like the third octet on a version number is a 10 and it treats 9 as the greater value. For example 2.6.10 is less than 2.6.9, but we know thats not right.

    So what I'm looking for is a way to zero pad the "software_ver" column so that I get the right version number.

    Thanks,
    tom

    Code:
    SELECT
    	*
    FROM
    	software q
    JOIN(
    	SELECT
    		bundle_id,
    		max(software_ver) AS max_app_ver
    	FROM
    		software
    	WHERE
    		app_state IN('Prod', 'QA')
    	AND active = '1'
    GROUP BY
    	bundle_id
    )a ON a.bundle_id = q.bundle_id
    AND a.max_app_ver = q.software_ver
    AND q.active = '1'
    AND q.app_state IN('Prod', 'QA')

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    you need to convert the "software_ver" column to an integer and then look for the max.

    Code:
    SELECT
    	*
    FROM
    	software q
    JOIN(
    	SELECT
    		bundle_id,
    		max(convert(software_ver, signed)) AS max_app_ver
    	FROM
    		software
    	WHERE
    		app_state IN('Prod', 'QA')
    	AND active = '1'
    GROUP BY
    	bundle_id
    )a ON a.bundle_id = q.bundle_id
    AND a.max_app_ver = q.software_ver
    AND q.active = '1'
    AND q.app_state IN('Prod', 'QA')
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Tom,

    ignore my previous post. I half read the post. I realize that having a version xx.yy.zz cannot be converted to a number. Instead this could be solved using a stored function.

    I have added the code below:

    Code:
    DROP FUNCTION IF EXISTS pversion;
    delimiter $$
    CREATE FUNCTION pversion(p_ver VARCHAR(20))
    RETURNS VARCHAR(20)
    DETERMINISTIC
    BEGIN
    	DECLARE l_start INT;
    	DECLARE l_end INT;
    	DECLARE l_maj VARCHAR(5);
    	DECLARE l_med VARCHAR(5);
    	DECLARE l_min VARCHAR(5);
    
    	SET l_start = 1;
    	SET l_end = LOCATE('.', p_ver, l_start);
    	IF (l_start + 1) = l_end THEN
    		SET l_maj = CONCAT('0',SUBSTR(p_ver, l_start, 1));
    	ELSE
    		SET l_maj = SUBSTR(p_ver, l_start, 2);
    	END IF;
    	SET l_start = l_end+1;
    	SET l_end = LOCATE('.', p_ver, l_start);
    	IF (l_start + 1) = l_end THEN
    		SET l_med = CONCAT('0',SUBSTR(p_ver, l_start, 1));
    	ELSE
    		SET l_med = SUBSTR(p_ver, l_start, 2);
    	END IF;
    	SET l_start = l_end+1;
    	SET l_end = LOCATE('.', p_ver, l_start);
    	IF LENGTH(SUBSTR(p_ver, l_start)) = 1 THEN
    		SET l_min = CONCAT('0',SUBSTR(p_ver, l_start));
    	ELSE
    		SET l_min = SUBSTR(p_ver, l_start);
    	END IF;
    	RETURN CONCAT(l_maj,'.',l_med,'.',l_min);
    
    END;
    $$
    delimiter ;
    To call this you simply use the following:

    Code:
    SELECT MAX(pversion(software_ver)) FROM table;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT q.*
      FROM software q
    INNER
      JOIN ( SELECT t.bundle_id
                  , MAX(
                    001000000 * SUBSTRING_INDEX(t.software_ver,'.',1) +
                       001000 * SUBSTRING_INDEX(SUBSTRING_INDEX(t.software_ver,'.',2),'.',-1) +
                          001 * SUBSTRING_INDEX(t.software_ver,'.',-1) 
                       ) AS max_app_ver
               FROM software AS t
              WHERE t.app_state IN ('Prod','QA')
                AND t.active = '1'
             GROUP
                 BY t.bundle_id ) AS a
        ON a.bundle_id = q.bundle_id
       AND a.max_app_ver = 
                    001000000 * SUBSTRING_INDEX(q.software_ver,'.',1) +
                       001000 * SUBSTRING_INDEX(SUBSTRING_INDEX(q.software_ver,'.',2),'.',-1) +
                          001 * SUBSTRING_INDEX(q.software_ver,'.',-1)
    the above is untested, and i suspect it will only work when there are exactly 3 pieces to the version number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    21
    Thanks, this is great!
    This gives me something to go on. I will play with the code a bit and try to make it work since some of my app version numbers to have up to 5 places.

    Thanks again,
    tom

Posting Permissions

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