Fetch value from collection without foreach loop

If you know that the collection just has one item then you could simply use $someCollection->getFirstItem(). This will give you the first and in your case only item. You can then continue to use the getSomeField() etc on this object.

A second option is possible to use ->load($attribute_value, 'attribute_code'); on the object but this can add overhead if the table is of a large size.

I checked the Mage_Core_Model_Resource_Db_Abstract and Mage_Core_Model_Resource_Db_Collection_Abstract for any method that does this and there is none.

I think implementing your own ResourceModel is the best way. Just shot directly a query to the database and return the value.