I am trying to write a query to return the id of the latest version of a market index stored in a database.

SELECT miv.market_index_id market_index_id from ref_market_index_version miv 
INNER JOIN ref_market_index mi ON miv.market_index_id = mi.id 
WHERE mi.short_name='dow30' 
AND miv.version_num = (SELECT MAX(m1.version_num) FROM ref_market_index_version m1 INNER JOIN ref_market_index m2 ON m1.market_index_id = m2.id )

The above SQL statement can be (roughly) translated into the form:

SELECT some columns FROM SOME CRITERIA MATCHED TABLES 
WHERE mi.short_name='some name'
AND miv.version_num = SOME NUMBER

What I don't understand is that when I supply an actual number (instead of a sub query), the SQL statement works - also, when I test the SUB query used to determine the latest version number, that also works - however, when I attempt to use the result returned by sub query in the outer (parent?) query, it returns 0 rows - what am I doing wrong here?

Incidentally, I also tried an IN CLAUSE instead of the strict equality match i.e.

... AND miv.version_num IN (SUB QUERY)

That also resulted in 0 rows, although as before, when running the parent query with a hard coded version number, I get 1 row returned (as expected).

BTW I am using postgeresql, but I prefer the solution to be db agnostic.