Query not giving required result using max function

[mithinxavier]

serial	rev
0	0
0	0
1303	1
0	0
0	0
0	0
1306	1
1301	1
1308	1
0	0
0	0
0	0
1295	1
1296	1
1297	1
1283	1
1283	2
1300	1
1299	1
1302	1
0	0
0	0
0	0
1012	5
1304	1
1305	1
0	0
0	0
0	0
0	0
0	0
1307	1
1309	1
1311	1
1312	1
1313	1
1315	1
1316	1
1310	1
0	0
0	0
0	0
0	0
0	0
1500	2A
1500	2B
1500	1

my code

SELECT projectDetails.serial, Max(projectDetails.rev) AS MaxOfrev
FROM projectDetails
GROUP BY projectDetails.serial
HAVING (((projectDetails.serial)<>0))
ORDER BY projectDetails.serial;


result :

serial	MaxOfrev
1012	5
1283	2
1295	1
1296	1
1297	1
1299	1
1300	1
1301	1
1302	1
1303	1
1304	1
1305	1
1306	1
1307	1
1308	1
1309	1
1310	1
1311	1
1312	1
1313	1
1315	1
1316	1
1500	2B

Desired Result

serial	MaxOfrev
1012	5
1283	2
1295	1
1296	1
1297	1
1299	1
1300	1
1301	1
1302	1
1303	1
1304	1
1305	1
1306	1
1307	1
1308	1
1309	1
1310	1
1311	1
1312	1
1313	1
1315	1
1316	1
1500	2A

1500 2B

[June7]

The query is working properly. 2B is greater than 2A. And 3A would be greater than 2B.

Also, because this is a text field, 2 would be greater than 11.

That's what happens when you mix alpha and numeric.

[mithinxavier]

The query is working properly. 2B is greater than 2A. And 3A would be greater than 2B.

Also, because this is a text field, 2 would be greater than 11.

That's what happens when you mix alpha and numeric.

How can i solve this issue

[June7]

Not easily. How many different alpha values are there - A thru Z? Never more than 1 alpha? Does number part ever exceed 9?

[mithinxavier]

Number part will be less than 9 only and alpha max up to d

[June7]

Wait, just took another look at your desired results. Why is that 1500 2B showing at end of post? You want both 2A and 2B records? If not, consider:

Query1:
SELECT projectDetails.Serial, projectDetails.rev, Val([rev]) AS N, Mid([rev],2) AS A
FROM projectDetails;

Query2:
SELECT * FROM Query1 WHERE Serial & rev IN (SELECT TOP 1 Serial & rev FROM Query1 AS Dupe WHERE Dupe.Serial = Query1.Serial ORDER BY Dupe.N DESC, Dupe.A ASC)
AND Serial<>'0' ORDER BY Serial;

[mithinxavier]

Thank you i solve the issue