I don't understand your data manipulation requirements. It would be better if you edit your post to put your code in between <code>...</code> tags.

I think you could save a lot of work by pushing the data manipulation into the database instead of running a loop in Perl to do the data manipulation. If you need the count of FIELD1 that are equal, that's just some basic SQL:

select field1, count(*)
from mytable
group by field1
[download]

Your other query seems to have some more convoluted logic, but if you can reformulate it in a more descriptive than prescriptive way, it will be translatable to SQL far more easy. You use vocabulary like "loop until find a valid entry", which I guess means "not equal to '-'". My guess at it is the following, but I'm unclear on whether you want to actually manipulate the data or just return a result:

select field1
from mytable l
where field2 <> '-'
order by field1, field2
[download]

or, if you only want all the "first" rows of field1 where field2 is not '-', then maybe the following returns what you need:

select field1, min(field2)
from mytable l
where field2 <> '-'
group by field1
order by field1, field2
[download]

hi guys, thanks for the replies. i still haven't solved the issue, but you made me think about using an hybrid solution.
i thought about using mysql directly to manipulate fields, however it is possible that my knowledge/experience of mysql is just not good enough!
i can't take a blanket query like the one suggested as records are chronological and depend on each other.
filtering out all rows with a field2 containing '-' is not good enough as i need to substitute the value with a 'real' string which is extracted comparing field1 in either the previous or next row if the values in field 1 are the same.(if anyone has suggestions please pass them on!)
however it was very useful to consider your suggestion to consider the query with min(fieldX) as i queried all the single entries matching entries with a 'real' value.

select pKey, field1, min(field2)
from myTable
where field2 <> '-' and pKey<20000
group by field1
order by pKey,field2
[download]

what i thought after this is that i can pass the values to a much smaller 'reference' array and use another pass to check rows with the reference and substitute where necessary.
something i still have issues in grasping where is ideal to prepare and execute the queries in the loop, therefore suggestions will be much appreciated!

  $dbi_dsn = "dbi:ODBC:<Name of Database>";
  #############################
  # connect to database
  $dbh = DBI->connect($dbi_dsn) or die "Can't connect to $dbi_dsn: $DB
+I::errstr";

   $sth = $dbh->prepare(“Call you query here”);
   $sth->execute();
   while ($hash_ref = $sth->fetchrow_hashref) {
    $field_1_value = $hash_ref->{Field1};
    ...
    $field_4_value = $hash_ref->{Field4};
   }
[download]