Garret Duffy, thanks for uploading sample data, it helps to troubleshoot and explain what is fouled up.
As I suspected, the problem is with the elevation values being returned by the subquery and how the IN clause is structured. The elevation values are fine, in and of themselves, and the subquery and query are returning correct results based on the SQL as written. Let's take a closer look at the example in your attached screenshot:
The Sample_Dataset table is in the background with the results of your subquery in the foreground. Although the subquery is calculating the maximum elevation value by the FID_T_25_projected column, it is only returning a result set of elevation values. With the query as written, two rows with FID_T_25_projected equal to 663 are being returned instead of one because there are two 663 records that have an elevation matching one of the elevation values in the subquery.
Even if you were to include FID_T_25_projected in the SELECT list of the subquery, the query as written still wouldn't return the results you want. If you were working with one of the database platforms that support multiple column IN clauses, like Oracle, a fairly small change to your SQL would get things workings. Unfortunately, neither file nor personal geodatabases support such functionality.
There are numerous ways to solve this riddle using SQL natively within a DBMS, but you are working within the confines of ArcGIS and the limitations that imposes on structuring SQL. Fortunately, you are working with personal geodatabases so there is a fairly straightforward solution:
objectid IN (
SELECT objectid
FROM sample_dataset t1
,(SELECT fid_t_25_projected, MAX(elevation) as maxe
FROM sample_dataset
GROUP BY fid_t_25_projected) t2
WHERE t1.fid_t_25_projected = t2.fid_t_25_projected
AND t1.elevation = t2.maxe)
The above SQL works with personal geodatabases and desktop geodatabases/personal SDE, but it doesn't work with file geodatabases or shapefiles. I suspect the above SQL would work with all enterprise geodatabases, but I haven't verified it.