I have a sql that I am using to pick up the highest version of the document number. Document number is a combination of doc_key, supplement_Seq, and reissue_seq
example doc_key = 1000, supplement_seq = 22, reissue_seq = 3
document number is 1000-22-3

sSQL = "select max(doc_key ||'-'||supplement_seq||'-' ||reissue_seq) DocId"
sSQL = sSQL & " from tbl_documents "

doc_key, supplement_seq, and reissue_seq are primary keys

The sql doesn't work correctly since I put a "-" as part of the result in the MAX function.
Example,

doc_key Supplement_Seq Reissue_seq
1000 5 0
1000 6 1
1001 2 0
1001 11 0

Correct result should have been 1001-11-0 but using the sql above I am getting 1001-2-0 as result since 2 comes before 1 (from the string 11-0 and 2-0)

I am not sure if the question is clear to you. Basically I need to have a sql that would work correctly to sort three fields and find the correct document number and show the result in ####-##-# format. I have tried the following sql and cannot get the correct result either

sSQL = "select max(doc_key)||'-'||max(supplement_seq)||'-' ||max(reissue_seq) DocId"
sSQL = sSQL & " from tbl_documents "

This sql would pick up the highest doc_key then combine with the highest supplement_seq and then the highest reissue_seq in the table which will not give me the correct result.

Example

doc_key Supplement_Seq Reissue_seq
1000 5 0
1000 6 1
1001 2 0
1001 11 0

correct result should have been 1001-11-0 but using the sql above, I get 1001-11-1 since 1001 is the max, 11 is the max and 1 is the max in all three fields. Hopefully it is not to complicated for you to see what I am trying to say. If you have any suggestions, please let me know. Thanks.