Select Max on two character column

MySQL database
#1

I have a table that stores information about drawings & documents.
A drawing has a document number (idn for internal document number) and a revision.
A document will start out at Revision A
Each time some issue is identified, the document will be fixed up
and this will have the same document number but the next highest revision.
A → B → C and so on.
When someone wants to view the drawing, they usually want to look at the newest revision.
I used the following query to find it
$query = “SELECT MAX(rev) FROM document_master where idn=$idn”;
in the example below it’s drawing 123 so the query is
SELECT MAX(rev) FROM document_master where idn=123

That served me well until we had a drawing with more than 26 changes.
So after rev Z of the drawing the next rev was set as rev AA.
This is the same as excel does with columns. AA is the next column after column Z.
Problem is max() does not work any more. It still finds the max as Z.

QUESTION
What is the SQL to find the highest revision in the column that has AA follow Z? Can it be done?
if it cant be done with a SQL query, how to do it programmatically?

…id…idn…rev. title
12345 236… A… How to assemble a thing
12678 236… B… improved how to assemble
15678 236… C… etc
… … … …
16890 236… Z… wow we have a lot of changes
17123 236. AA… and even more
17125 236. AB ← This is currently the maximum revision.

#2

Swap your revisions to use numbers in the database. If you want to show letters to the user the following function will work:

/**
 * Given an integer greater than zero, return the "Excel style" column name
 *
 * eg:
 *   1 => 'A'
 *   2 => 'B'
 *  26 => 'A'
 *  27 => 'AA'
 *  52 => 'AZ'
 */
function convert(int $input): string
{
    $out = '';
    $carry = $input;
    while ($carry > 0) {
        $current_letter_as_num = ($carry - 1) % 26;
        $current_letter = chr($current_letter_as_num + 65);
        $out = $current_letter . $out;
        $carry = ($carry - $current_letter_as_num - 1) / 26;
    }

    return $out;
}
#3

Or just use dates as most programmers use. Not Revision#a-z, but, Revised: 9-3-2021…

#4

This was the eventual solution - to create a number representation of the human readable document revision letter and number format and then sort on that.

function numerical_revision($rev)
{
	// Document revisions start off as single letters but when we get to rev Z,
	// go to AA
	// The problem is how to assign a numerical number to rev 
	// to allow us to find the maximum rev.
	// 
	//  A
	//  B
	// ...  skip a few
	//  Z
	// AA
	// AB3
	// || \
	// | \ the three will be added in as point 3
	// |  This is the least Significant digit and it's worth ord('B')
	// |    Note 26^0 = 1
	// This is worth 26^1 = 26 * ord('A') 
	//echo "\n<BR><font color='blue'>UHp1 dB_fns>numerical_revision(rev=$rev)</font>";
	
	// 1. Split REV into the letters and numbers bit
	// $rev  AB4
	$m = $rev;          // This is like a running total
	$n='';              // number part
	
	while (strlen($m)>0)
		{
		$x = $m[strlen($m)-1];      // single character Last character
        if (is_numeric($x))
			{
			$n = $x.$n;
		    $m = substr($m, 0, strlen($m)-1);  // chop off that last character
			}
		else
			{
			break;
			};
		//echo "\n<BR> rev=$rev m=$m  x=$x n=$n";
		};
	
	//2 Calculate the numerical value of the letters part
    $i = 0;  // Digit counter
	$mv = 0; // Numerical value of the letters.  cumulative
	
	while (strlen($m)>0)
		{
		//$x = $m[strlen($m)-1];      // single character Last character
		//$mv = $mv + (26**$i)*ord($m[strlen($m)-1]);
		$mv = $mv + pow(26,$i)*ord($m[strlen($m)-1]);
		//echo " xxx m=$m i=$i mv=$mv xxx ";
		$m = substr($m, 0, strlen($m)-1);  // chop off that last character
		$i++;
		};

    // 3 Add on the numbers bit as a decimal.
	$rev_number = $mv.".".$n;
	//echo " ----- m=$m mv=$mv rev_number=$rev_number";
return $rev_number;
#5

Another way might be to just use numerical values and convert to a revision letter.
This code can quickly create letters for you from “A” to “ZZ”. More than enough for revisions. (26 * 26)
And, then you can use INT for the value of the revision number and increment it when adding one.

<?php
//  Create range array from A to ZZ
$letters = range('A', 'Z');
// AA-ZZ combinations
foreach(range('A', 'Z') as $letter1) {
    foreach(range('A', 'Z') as $letter2) {
        $letters[] = $letter1 . $letter2;
    }
}
echo "<pre>" . print_r($letters,1) . "</pre>";
?>

That creates an array like “A,B,C…Z,AA,AB,AC…ZZ” and can be accessed using the index of the revision.
You might want to note that it makes the “A” indexed as zero, you might want to start it at index 1…

#6

Use a stored function in your database to convert A…ZZ to a number:
Preformatted text
DELIMITER $$
CREATE FUNCTION RevToNum( VARCHAR(2) rev ) RETURNS INT NO SQL DETERMINISTIC
COMMENT ‘Convert A - ZZ to Integer’
BEGIN
IF LENGTH(rev)=1 THEN
RETURN ORD(rev);
ELSE
RETURN ( ORD( SUBSTR(rev,1,1) )*256 ) + ORD(SUBSTR(rev,2,1));
END IF;
END$$

Test:

SELECT RevToNum('A'),RevToNum('Z'), RevToNum('AA'), RevToNum('AZ'), RevToNum('ZA'), RevToNum('ZZ')
Sponsor our Newsletter | Privacy Policy | Terms of Service