If you are in a hurry, then just scroll down to the end of the article to see my answer to the problem.

Sometimes you have to use complex queries for ex. collections, which will be really an ugly code. Since direct query is not a real option, we have to go with the Magento way. This is all nice and beautiful, until you realize, that your idea is not possible with the Magento-Zend selects. Lets see an example, first with the needed result:

SELECT `main_table`.*, (SELECT `b`.`name` FROM `names` AS `b` WHERE `b`.`customer_id` = `main_table`.`customer_id` ORDER BY `b`.`name` DESC LIMIT 1) AS `names_name`
FROM `sales_flat_order_grid` AS `main_table` 
WHERE `main_table`.`group_id` = 1

You can say that this is an ugly query which can be replaced with joins and maybe with other methods, but we are talking about the unquoting something from the query.
So, the query above translated to a collection will look something like:

$collection = Mage::getResourceModel("sales/order_grid_collection")
->addExpressionFieldToSelect("names_name", "(SELECT `b`.`name` FROM `names` AS `b` WHERE `b`.`customer_id` = `main_table`.`entity_id` ORDER BY `b`.`name` DESC LIMIT 1)")
->addFieldToFilter("`main_table`.`group_id`", "1");

Ugly, isn’t it? And if we echo out the resulting query with echo $collection->getSelect(), it will be even more uglier:

SELECT `main_table`.*, `(SELECT ``b```.```name`` FROM ``names``` AS ```b`` WHERE ``b``.``customer_id`` = ``main_table``.``entity_id`` ORDER BY ``b``.``name`` DESC LIMIT 1)` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (`main_table`.`group_id` = '1')

Lets get rid of the double and triple quotes by changing our addExpressionFieldToSelect:

->addExpressionFieldToSelect("names_name", "(SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)")

Now it is much better, but still not ok:

SELECT `main_table`.*, `(SELECT b`.`name FROM names` AS `b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (group_id = '1')

Our whole subselect is quoted and it is not aliased at all. Here comes the trick. We will have to use the Zend_Db_Expr for this and we will put this new column directly into our select with $collection->getSelect()->columns() method. See it how:

$collection = Mage::getResourceModel("sales/order_grid_collection");
$collection->getSelect()->columns(array("names_name" => new Zend_Db_Expr("(SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)")));
$collection->addFieldToFilter("`main_table`.`group_id`", "1");

The resulting sql will be:

SELECT `main_table`.*, (SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1) AS `names_name` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (`main_table`.`group_id` = '1')

The only thing you need to be careful is to have your subselect between brackets, so your sql will remain valid.