problem in finding maximum value

[masoud_sedighy]

I have a table , it has 2 field (docno,rev) and type of both of them is (text)
now i would like to return maximum value of (rev) for each (docno)

data of my table is like below:


docno rev

doc-0001 1
doc-0001 2
doc-0001 2a
doc-0001 11
doc-0002 5
doc-0002 10
doc-0002 3


now i would like to return result like below:

doc-0001 11
doc-0002 10

i used below query:


SELECT Table1.docno, Max(Table1.rev) AS MaxOfrev
FROM Table1
GROUP BY Table1.docno;

it returns like below, how i can solve my problem


docno MaxOfrev

doc-0001 2a
doc-0002 5

 

[plog]

If you wanted to treat your data as numeric, you need to store them as numeric. Access compares values differently based on the data type it is using--that field us text so it compares the data in it as such. 2 A is the maximum value when doing so.

 

[masoud_sedighy]

I could not define as number , i have different value in "rev" field some of them are like numbers and some are number+alphabet
, i would like in the query for example:

1<2a<3

I mean number is main part of my "rev" field , is there any way?

 

[Minty]

You can use Val() to return the value of a number, however there are restrictions. See http://www.techonthenet.com/access/functions/numeric/val.php for a full explanation, note it wont help with a text values like 2a3 , 2a4 for example.

 

[arnelgp]

you need a function to do that for you.
paste on a Module this public function.

Dim sChar As String
Public Function fnMaxStr(p As Variant) As Double
    Dim i As Integer
    Dim j As Integer
    Dim v As Integer
    If sChar = "" Then
        sChar = "/"
        For i = 0 To 25
            sChar = sChar & Chr(65 + i) & "/"
        Next
    End If
    p = p & ""
    For j = 1 To Len(p)
        v = v + getIndex(Mid(p, j, 1))
    Next
    p = Val(Val(p) & "." & v)
    fnMaxStr = p
End Function

Private Function getIndex(p As String) As Integer
    Dim i As Integer
    i = InStr(sChar, "/" & p & "/")
    getIndex = i
End Function

Now create a query like this, calling our function:

SELECT DISTINCT T1.docno, (SELECT TOP 1 REV FROM [COLOR=Blue]docu[/COLOR] WHERE [COLOR=Blue]docu[/COLOR].docno = T1.docno ORDER BY fnMaxStr([Rev]) DESC) As LastRev FROM [COLOR=Blue]docu[/COLOR] AS T1;

replace docu with correct table name you have.

 

[Simon_MT]

With a Sring value with two characters 1=10, 2=20 etc - if you add a space before a single character and the value is treated as 1,2 etc

Simon

 

[masoud_sedighy]

with using val() function and sub-query I used below query,it seems also this one works.

SELECT T1.docno, (SELECT TOP 1 rev from (SELECT Table1.docno, Table1.rev, Val([rev]) AS new_rev FROM Table1 WHERE (((Val([rev]))=(SELECT max(val(rev)) FROM table1 AS T2 WHERE T2.docno=table1.docno)))) AS T3 where T3.docno=T1.docno order by rev DESC) AS [last rev]
FROM (SELECT table1.docno, Val(rev) AS new_rev, table1.rev FROM table1) AS T1
WHERE (((T1.new_rev)=(select max (T2.new_rev) from (SELECT docno,rev,val(rev) AS new_rev from table1) AS T2 WHERE T2.docno=T1.docno)))
ORDER BY T1.docno;

 

[arnelgp]

it will work if you only have numeric data in your field. i am in construction too and i know how iso drawing numbers are numbered. i guess you are getting results like revision = 11 and 10, but how about if there is revision 11A or 10B?

 

[masoud_sedighy]

yes you are right my query did not work for some cases and also it was slow.

i checked with another query (group by)

first tried to catch date when val(rev) is max for each docno then with group by query get max(rev) like below
i checked it works, but i would like to do it totaly with sub-query,

is there any way for doing that totaly with sub-query?


SELECT T1.docno, Max(T1.rev) AS MaxOfrev
FROM (SELECT table1.docno, Val(rev) AS new_rev, table1.rev FROM table1 ORDER BY table1.docno, Val(rev) DESC , table1.rev DESC) AS T1
WHERE (((T1.new_rev)=(select max (T2.new_rev) from (SELECT docno,rev,val(rev) AS new_rev from table1) AS T2 WHERE T2.docno=T1.docno)))
GROUP BY T1.docno
ORDER BY T1.docno, Max(T1.rev);

 

[Galaxiom]

Avoid the complexity by separating out the alpha sub-revision into a different field.