Manipulating Query result to show MAX data grouped by a field

Question

Here is my existing query:

$Pareto = mysql_query("SELECT
      zsca_open.tqs_batch_num AS 'Batch Number',
      ft_tests.test_name AS 'Test Name',
      (SUM(ft_stats.over_filter + ft_stats.under_filter)) AS 'Failures'
     FROM
      ((zsca_open INNER JOIN ft_header ON zsca_open.tqs_batch_num = ft_header.batch_code)
      INNER JOIN ft_tests ON ft_header.serial_hi = ft_tests.serial_hi) 
      INNER JOIN ft_stats ON ft_tests.serial_si = ft_stats.serial_si
     WHERE
      (((zsca_open.vendor)='ASE')
      AND ((zsca_open.lot_type)='T')
      AND ((zsca_open.status)='H')
      AND ((zsca_open.is_active)=1)
      AND ((zsca_open.rec_ver)=0)
      AND ((ft_header.test_step)='PROD')
      AND ((ft_header.status)='VIRGIN')
      AND ((ft_stats.stats_name)='spec_stats'))
     GROUP BY
      zsca_open.tqs_batch_num,
      ft_tests.test_name
     HAVING
       (((ft_tests.test_name) Not In ('SW Bin','HW Bin','Pass')))
     ORDER BY
      SUM(ft_stats.over_filter + ft_stats.under_filter) DESC") or die(mysql_error());

HERE IS THE OUTPUT:

Batch Number TestName Failures <br>
0938AT5213   PCS_Pout 2865 <br>
0939AT5228 LB_PAE 921 <br>
0938AT5213 DCS_Pout 769 <br>
0939AT5228 LB_Pout_Nom 730 <br>
0939AT5228 LB_Pout 642 <br>
0938AT5213 DCS_PAE 517 <br>
0919AT3366 Iswitch 513 <br>
0938AT5213 PCS_PAE 481 <br>
0939AT5228 RX2_Insert_Loss 474 <br>
0939AT5228 HB_PAE 470 <br>
0939AT5228 HB_Pout 434 <br>
0939AT5228 HB_Pout_Nom 426 <br>
0919AT3366 ACLR1L_H1_846p6 399

The output I need is to show only the MAX Failures per Batch Number.

Batch Number test_name Failures <br>
0938AT5213 PCS_Pout 2865 <br>
0939AT5228 LB_PAE 921<br>
0919AT3366 Iswitch 513

Canyou help me with the query? Thanks in advance

Answer 1

this is really easy if you don't need the test_name field. if this is the case, just wrap your whole query in:

SELECT `Batch Number`, max(Failures) from (**YOURQUERYHERE**) as myquery GROUP BY Failures;

this will give you the max failures by batch number, but won't give you the test_name that corresponds to the max failures. in order to get the test_name, you need to do a subquery join, as demonstrated here. Or here.

in your case, since your query is already pretty complicated, i'd recommend turning it into a temporary table:

CREATE TEMPORARY TABLE mytable (batch varchar(30), testname varchar(30), failures INT);
INSERT INTO mytable (**YOURQUERYHERE**);

then you can get the max by doing one of the steps recommended on those links. for instance:

SELECT t1.batch, t1.testname, t1.failures
  FROM mytable AS t1,
       (SELECT batch, MAX(failures) AS maxfail
          FROM mytable
         GROUP BY batch) AS t2
  WHERE t2.batch = t1.batch
        AND t1.failures = t2.maxfail;

Answer 2

In MySQL you can nest queries, so you can treat the result of your initial query as a temporary table like:

SELECT
        batches.'Batch Number',
        tests.'Test Name',
        batches.'Failures'
FROM
(
        SELECT
                zsca_open.tqs_batch_num AS 'Batch Number',
                MAX(SUM(ft_stats.over_filter + ft_stats.under_filter)) AS 'Failures'
        FROM
                ((zsca_open INNER JOIN ft_header ON zsca_open.tqs_batch_num = ft_header.batch_code)
                INNER JOIN ft_tests ON ft_header.serial_hi = ft_tests.serial_hi)
                INNER JOIN ft_stats ON ft_tests.serial_si = ft_stats.serial_si
        WHERE
                (((zsca_open.vendor)='ASE')
                AND ((zsca_open.lot_type)='T')
                AND ((zsca_open.status)='H')
                AND ((zsca_open.is_active)=1)
                AND ((zsca_open.rec_ver)=0)
                AND ((ft_header.test_step)='PROD')
                AND ((ft_header.status)='VIRGIN')
                AND ((ft_stats.stats_name)='spec_stats'))
        GROUP BY
                zsca_open.tqs_batch_num
        HAVING
                (((ft_tests.test_name) Not In ('SW Bin','HW Bin','Pass')))
) batches
INNER JOIN ft_header ON tests.'Batch Number' = ft_header.batch_code
INNER JOIN (
        SELECT
                ft_tests.serial_hi,
                ft_tests.test_name AS 'Test Name',
                SUM(ft_stats.over_filter + ft_stats.under_filter) AS 'Failures'
        FROM
                ft_tests
        GROUP BY
                ft_tests.serial_hi,ft_tests.test_name
) tests ON (ft_header.serial_hi = tests.serial_hi AND test.'Failures' = bartches.'Failures')

This is a first guess but it essentially is the result of writing out the various "views" you need to perform the query in one go and then treating the SELECT statements as tables.

This may also be quite inefficient so you may want to consider alternative methods like using MySQLs views or doing the final aggregation in code.

If you want more information on the technique used in my query then check out this page on the MySQL site.

Answer 3

Hi

Thanks for your reply. I tried a lot of things already. Unfortunately I cannot create a table on our database.

Can you help me write a query that will give me the max failures per batch number without the test)names instead?

I can work aroud with the queries once I have that result.

Thanks again.