query

[leoliang]

hi,
here is the 3 situation in my table
1.
id revision
1 0
1 2
1 A
1 B
result should be 2
2.
id revision
1 A
1 B
1 C
result C
3.
id revision
1 1
1 3
result 3
the above tables are examples after "group by"
i wanna select the max number value in a field, but this field contain number and letter, in the case only letter exists, select the max letter value.

anyone can give me a hint ? thanks a lot

 

[SonOfEmidec1100]

Heres my attempt should give you a hint

SELECT type, id, ltrim(MAX(CASE WHEN ISNUMERIC(revision) <> 0 THEN revision ELSE ' ' + revision END)) AS MaxRevision
FROM MyTable1
GROUP BY type, id
ORDER BY type, id

 

[leoliang]

Thank you for taking the time to reply. Unfortunately this did not work.

my db is ms access, probably it dont know &quot;case&quot; statement

the error message shows below

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ltrim(MAX(CASE WHEN ISNUMERIC(rev) <> 0 THEN rev ELSE ' ' + rev END))'.

 

[SonOfEmidec1100]

This is the SQL Server forum, try reposting in
Microsoft: Access Queries and JET SQL forum