Retrieve specific rows with the largest value.

[PRPhx]

I have a table that I am trying to retrieve rows that include the largest value (docid)for a group of similiar rows.

The fields I am interested in are the equipment# (varchar) and docid (integer). As the table is very large (approx. 1 million rows) I am searching by the 2 character equipment prefix.

My question is: How do I retrieve each groups row with the largest docid??? (FYI, the docid is tied to a tiff image. There are approx 87,000 distinct equipment numbers)
I've tried the following:

SELECT field1,docid
FROM Trailer_Regs
WHERE field1 LIKE 'AV%' AND (docid =(SELECT MAX(docid) FROM Trailer_Regs))
GROUP BY ALL field1, docid
HAVING field1 LIKE 'AV%'
ORDER BY field1, docid DESC

the AV is one of the equipment number prefixes that denotes it as a trailer.

Below is a sample of the output the query returned. What I need is the equipment number with the highest docid.

Equip # DocID
AV 8181C 924253 <--
AV 8181C 279113
AV 8191C 895436 <--
AV 8191C 246796
AV 8194C 924254 <--
AV 8194C 279114
AV 8444C 895437 <--
AV 8444C 246797


How do I retrieve just the rows indicated by the <-- ???
NOTE: Not all equipment numbers have spaces between the alpha and numeric parts, if that makes any difference.


Thanks in advance for your suggestions.

 

[gmmastros]

SELECT field1,Max(docid) As DocId
FROM Trailer_Regs
WHERE field1 LIKE 'AV%' 
GROUP BY field1
ORDER BY field1 DESC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fredericofonseca]

as an alternative just in case you do need more fields than just field1 and the max(docid).

select field1, field2, field3, docid
from trailer_regs
where (field1, docid) in
(SELECT field1, Max(docid) As DocId
FROM Trailer_Regs
WHERE field1 LIKE 'AV%'
GROUP BY field1)

ORDER BY field1 DESC



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com