SelectQuery row_number won't work with PDO
In order to count the number of occurence you can use groupBy and having cluases but as you mention above your format is completely different. So it is not feasible to you. Try this one may be it'll helpful to you.

$query = db_select('temp', 't');
$query-&gt;fields('t',array('a','b'));
$query-&gt;orderBy('t.a');
$result = $query-&gt;execute();

$field_sum = 1;
$prev = NULL;
foreach ($result as $record) {
   if ($prev ==  $record-&gt;a) {
      $field_sum++;
   } 
   else {
   $field_sum = 1;
   }

   echo "\n a " . $record-&gt;a . " | b " . $record-&gt;b . " | Counter ". $field_sum; 
   $prev = $record-&gt;a;
}


Also ensure that these list of function can't be chained:

addExpression()
addField()
addJoin()
extend()
innerJoin()
join()
leftJoin()
rightJoin()