Your program and example output do not help us. Please see SSCCE for how to create an example that allows us to reproduce your problem.

From looking at your data, you seem to want to sort your list according to two criteria at the same time. This makes no sense.

Create two SQL queries and have the sorting there, according to the order you need. Then run each query separately.

Create two SQL queries and have the sorting there, according to the order you need. Then run each query separately.

I disagree, if only on a technicality. Back to the idea that these are statistics from a batch scheduler, the chance that the underlying data may have changed between the separate query runs is not zero. Instead i would run one select so there is just a single select query cursor instance, and sort the data separately in perl as in

use strict; use warnings; 
use DBI; 
use JSON 'encode_json';
use cheap::mysqls qw/sql_connect/; 

  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'mysql');


  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->do('create  
            view datas as
                 select queue_name as name, 
                       jobs_pend   as data1, 
                       jobs_run    as data2
                   from queues
           '); 

{
my @col1; 
my @col2; 
my @oth1; 
my @oth2; 

    my $stmt="select name,data1,data2 from((select name,data1,data2 fr
+om datas order by data1 +data2 desc limit 5  )union all(select 'added
+' as name ,sum(data1)  as data1 ,sum(data2) as data2 from (select dat
+a1,data2 from datas order by data2+data1 desc limit  1844674407370955
+1615 offset 5 ) new)) new;";
    
    my $sth = $DBH->prepare( $stmt );
    $sth->execute() or die $sth->errstr;
    
    my $tmp = 0;
    while(my @row_array=$sth->fetchrow_array)
    {
          if ($row_array[0] ne 'added') { 
             push @col1,[$row_array[0],$row_array[1]]; 
             push @col2,[$row_array[0],$row_array[2]]; 
             } 
          else { 
             push @oth1,[$row_array[0],$row_array[1]]; 
             push @oth2,[$row_array[0],$row_array[2]]; 
             } 
    }
    $sth->finish;

my @sort1=((sort {$b->[1] <=> $a->[1]} @col1),@oth1); 
my $json1 = encode_json( \@sort1 );
print $json1."\n"; 

my @sort2=((sort {$b->[1] <=> $a->[1]} @col2),@oth2); 
my $json2 = encode_json( \@sort2 );
print $json2."\n"; 

}
  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->disconnect();
[download]

Since they included the json producing code for the creating of the pie charts, i have done so as well. That also explains why they want the separate sorts.

To also add to the incomplete specs in the OP, notice how what was once called the others row has become the added row, yet that is not exhibited in the expected output.

cheap::mysqls is a module in my private library that allows me to easily connect to my mysql and sqlite databases. you may use whatever method you like to connect to yours instead.

Surely order by data1+data2 gives the same top 5 names just sorted differently whereas if you do separate queries the top 5 names might be different. To do both in 1 query I guess you could do something complicated like this

#!perl
use strict; 
use DBI; 

my $DBH; my $data1; my $data2;
datas('dbname');
print "$data1\n$data2";

sub datas
{
  $data1 = "";
  $data2 = "";
  my $str = shift;
  $DBH = &connect or die "Cannot connect to the sql server \n";
  $DBH->do("USE $str;");
  my $stmt="
( select name,data1
  from datas 
  order by data1 desc 
  limit 5 )

union all
( select 'added' ,sum(data1) 
  from (
    select data1
    from datas 
    order by data1 desc 
    limit  18446744073709551615 offset 5
  ) t1
)

union all
( select name,data2
  from datas 
  order by data2 desc 
  limit 5 )

union all
( select 'added' ,sum(data2) 
  from (
    select data2
    from datas 
    order by data2 desc 
    limit  18446744073709551615 offset 5
  ) t1
)";
    
  my $sth = $DBH->prepare( $stmt );
  $sth->execute() or die $sth->errstr;
    
  my $tmp = 0;
  while(my @row_array=$sth->fetchrow_array)
  {
    if ($tmp > 5){
      if ($tmp == 6 ) {
        $data2 = "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]\
+]";
      }
      else {
        $data2 .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[1
+]\]";
      }
    } else {
       if ($tmp == 0 ) {
         $data1 = "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]
+\]";
       }
       else {
         $data1 .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[
+1]\]";
       }  
    }
    ++$tmp;
  }
  $sth->finish;
  $DBH->disconnect();
}
[download]

poj

erix's point was your code is not self contained enough for us to understand what you expect and what you see in your output. In other words, it does not follow the SSCCE guidelines. Please modify your post under an *EDIT*, including all information about expected output and actual results.