title: Magento - How to select mysql rows by max value?
body: <pre><code>mysql&gt; SELECT * FROM `log_customer` WHERE `customer_id` = 224 LIMIT 0, 30;
  +--------+------------+-------------+---------------------+-----------+----------+
  | log_id | visitor_id | customer_id | login_at            | logout_at | store_id |
  +--------+------------+-------------+---------------------+-----------+----------+
  |    817 |      50139 |         224 | 2011-03-21 23:56:56 | NULL      |        1 |
  |    830 |      52317 |         224 | 2011-03-27 23:43:54 | NULL      |        1 |
  |   1371 |     136549 |         224 | 2011-11-16 04:33:51 | NULL      |        1 |
  |   1495 |     164024 |         224 | 2012-02-08 01:05:48 | NULL      |        1 |
  |   2130 |     281854 |         224 | 2012-11-13 23:44:13 | NULL      |        1 |
  +--------+------------+-------------+---------------------+-----------+----------+
  5 rows in set (0.00 sec)
  
  
  mysql&gt; SELECT *  FROM `customer_entity` WHERE `entity_id` = 224;
  +-----------+----------------+---------------------------+----------+---------------------+---------------------+
  | entity_id | entity_type_id |  email                    | group_id | created_at          | updated_at          |
  +-----------+----------------+---------------------------+----------+---------------------+---------------------+
  |       224 |              1 |  damodar@stackoverflow.com.au |        3 | 2011-03-21 04:59:17 | 2012-11-13 23:46:23 |
  +-----------+----------------+---------------------------+----------+--------------+----------+-----------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>How can i search for customers who hasn't logged in for last 10 months and their account has not been updated for last 10 months. I tried below but failed.</p>
  
  <pre><code>$collection = Mage::getModel('customer/customer')-&gt;getCollection();
  $collection-&gt;getSelect()-&gt;joinRight(array('l'=&gt;'log_customer'), "customer_id=entity_id AND MAX(l.login_at) &lt;= '" . date('Y-m-d H:i:s', strtotime('10 months ago')) . "'")-&gt;group('e.entity_id');
  $collection-&gt;addAttributeToSelect('*');
  $collection-&gt;addFieldToFilter('updated_at', array(
      'lt' =&gt; date('Y-m-d H:i:s', strtotime('10 months ago')),
      'datetime'=&gt;true,
  ));
  $collection-&gt;addAttributeToFilter('group_id', array(
      'neq' =&gt; 5,
  ));
  </code></pre>
  
  <p>Above tables have one customer for reference. I have no idea how to use MAX() on joins. Thanks</p>
  
  <h3>UPDATE:</h3>
  
  <p>This seems returning correct data, but I would like to do magento way using resource collection, so i don't need to do load customer again on for loop.</p>
  
  <pre><code>$read = Mage::getSingleton('core/resource')-&gt;getConnection('core_read');
  
  $sql = "SELECT e.*,MAX(l.login_at) as login_at
      FROM `customer_entity` e
      LEFT JOIN `log_customer` l on e.entity_id=l.customer_id
      GROUP BY l.customer_id
      HAVING created_at &lt; '".date('Y-m-d H:i:s', strtotime('10 months ago'))."'
      and (login_at&lt; '".date('Y-m-d H:i:s', strtotime('10 months ago'))."' or login_at is null)
      and group_id != 5
      ORDER BY `e`.`entity_id` ASC";
  
  $result = $read-&gt;fetchAll($sql);
  </code></pre>
  
  <h3>I have loaded full shell script to github</h3>
  
  <ul>
  <li><a href="https://github.com/dbashyal/Magento-ecommerce-Shell-Scripts/blob/master/shell/suspendCustomers.php" rel="nofollow">https://github.com/dbashyal/Magento-ecommerce-Shell-Scripts/blob/master/shell/suspendCustomers.php</a></li>
  </ul>