gpssana has asked for the wisdom of the Perl Monks concerning the following question:

Here i had added my part of subroutine and mentioned only the part of the error which i struck with to complete it. 
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,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 ($tmp == 0) {
        $data1 .= "\[\"$row_array[0] \($row_array[2]\)\",$row_array[2]
+\]";
        
        $data2 .= "\[\"$row_array[0] \($row_array[1]\)\",$row_array[1]
+\]";
        $tmp++;
        }
        else {
        $data1 .= ",\[\"$row_array[0] \($row_array[2]\)\",$row_array[2
+]\]";
        $data2 .= ",\[\"$row_array[0] \($row_array[1]\)\",$row_array[1
+]\]";
        }
    
    }
    $sth->finish;
    $DBH->disconnect();
}

[download]

Here i hsd done sorting by considering the name common for both data1 and data2.Instead i should take name+data1 and name+data2 independently.
Here i want to sort the both data1 and data2 from maximum to minimum level.But from my above code only the data2 get sorted data1 is not sorted from maximum to minimum level.
How can sort both columns independently
. Help me to fix my mistake.Thanks in advance for any help. 
obtained output:

+--------------+--------------+--------------+
|       name   | data1        |        data2 |
+--------------+--------------+--------------+
| dsp_ncsim_hp |        11262 |           82 |
| adice_short  |         2865 |          274 |
| ncsim_long   |          741 |          116 |
| adice_ncsim  |          109 |          150 |
| normal       |          133 |           31 |
| others       |            5 |          184 |
+--------------+--------------+--------------+
Expected:

Jobs_running need to be changed.Here jobs_running column is fetching a
+s per jobs_pending column.Jobs_pending should be sorted from maximum 
+to minimum level as per queue_name.Here jobs_running column is not ar
+ranged from maximum to minimum level.

+--------------+--------------+--------------+
| queue_name   |        data1 |        data2 |
+--------------+--------------+--------------+
| dsp_ncsim_hp |        11262 |           82 |
| adice_short  |         2865 |          274 |
| ncsim_long   |          741 |          116 |
| adice_ncsim  |          109 |          150 |
| normal       |          133 |           31 |
| others       |            5 |          184 |
+--------------+--------------+--------------+
In the above table running is not in this order.How can i sort jobs_ru
+nning and jobs_pending independently by its value.

For jobs_running:-

    +-------------+--------------+--+
    |   name       |  data1        |
    +--------------+---------------+
    |adice_short    |          274 |
    | adice_ncsim   |          150 |
    | ncsim_long    |          116 |
    | dsp_ncsim_hp  |          82  |
    | normal        |          31  |
    | others        |         184  |
    +--------------+---------------+
For jobs_pending:-

    +--------------+--------------+
    | name         | data2       |
    +--------------+--------------+
    | dsp_ncsim_hp |        11262 |
    | adice_short  |         2865 | 
    | ncsim_long   |          741 | 
    | adice_ncsim  |          109 |  
    | normal       |          133 |   
    | others       |            5 | 
    +--------------+--------------+

[download]

Replies are listed 'Best First'.
Re: perl sql to separate and sort the column separately?
by erix (Prior) on Apr 12, 2017 at 07:12 UTC

    Please try to make your post into a self-sufficient example so that others can see for themselves what goes wrong when they run it on their own database.

[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: perl sql to separate and sort the column separately?
by chacham (Prior) on Apr 12, 2017 at 18:42 UTC

    I am not sure i understand the issue here. I'm having a hardtime understanding it from you example and explanation.

    Now, some general comments on you code:

    $DBH = &connect or die "Cannot connect to the sql server \n";

    This is mysql, right? SQL Server is another product and may confuse you at a later date. Consider changing the message to "Cannot connect to database."

    datas

    A nitpick, but, "datum" is singular, "data" is plural. "Datas" is just plain wrong. :) Although rarely done, if you want to be correct, change the columns to "datum1" and "datum2" and call the table "data".

    There is no reason to place the query-parts in parenthesis. UNION ALL works just fine without them.

    The second query-part does not need aliases. They will be ignored anyway. Column names in a UNION (ALL) always follow the names in the first query-part.

    Both subtables are aliased "new". While the name is not referred to, and context will separate them, it is best to use different names to avoid confusion. Further, the name "new" is a poor choice, as it is often a keyword and can cause errors or confusion.

    While there is an ORDER BY in the sub-queries to help with LIMIT and OFFSET, there is no ORDER BY on the outer query. Usually this isn't a problem, as a UNION ALL places this results of the second part after those of the first part. But this is not how it has to be, it just happens to work out that way. If order is important, you should put an ORDER BY on the outer query, and remember to use column ordinals and not names there, unless your RDBMS supports names there.

[reply]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom