Select Max on two character column

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.

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;
}

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

Sponsor our Newsletter | Privacy Policy | Terms of Service