 <p>This is an aggregation query, but you don't seem to want the column being aggregated. That is ok (although you cannot distinguish the rk that defines each row):</p> <pre><code>select count(*) as CountOfId, max(dateof) as maxdateof from t group by fk; </code></pre> <p>In other words, your subquery is pretty much all you need.</p> <p>If you have a reasonable amount of data, you can use a MySQL trick:</p> <pre><code>select substring_index(group_concat(id order by dateof desc), ',', 1) as id count(*) as CountOfId, max(dateof) as maxdateof from t group by fk; </code></pre> <p>Note: this is limited by the maximum intermediate size for <code>group_concat()</code>. This parameter can be changed and it is typically large enough for this type of query on a moderately sized table.</p>