MySQL GROUP BY MAX two columns

Question

So Ive got a number of tests in a database, each test belongs to a category and each "group" of tests has a "serial number" (timestamp of sorts). Each test has a pass or fail result, Like so:

TEST Table
TEST_ID     TEST_NAME    TEST_CAT    TEST_SN     RESULT
1           test_a       Basic       112233      Pass
2           test_b       Basic       112233      Pass
3           test_c       Basic       112233      Pass
4           test_d       Basic       112233      Fail
5           test_e       Basic       112233      Pass
6           test_a       Basic       223344      Fail
7           test_b       Basic       223344      Pass
8           test_c       Basic       223344      Pass
9           test_d       Basic       223344      Fail
10          test_w       Advacned    112233      Fail
11          test_x       Advacned    112233      Pass
12          test_y       Advacned    112233      Pass
13          test_z       Advacned    112233      Pass

Im trying to craft a query to return only the rows for each category where the TEST_SN is highest. So the result Im trying to return would look like this:

TEST_ID     TEST_NAME    TEST_CAT    TEST_SN     RESULT
6           test_a       Basic       223344      Fail
7           test_b       Basic       223344      Pass
8           test_c       Basic       223344      Pass
9           test_d       Basic       223344      Fail
10          test_w       Advacned    112233      Fail
11          test_x       Advacned    112233      Pass
12          test_y       Advacned    112233      Pass
13          test_z       Advacned    112233      Pass

Since There are TEST_SNs "112233" and "223344" for "Basic", I want to ignore all tests from the earlier (smaller) TEST_SN (rows 1,2,3,4 and 5). Since "112233" is the only TEST_SN for Advacned, I want to keep all of those rows (10,11,12 and 13).

I can come close with this query:

SELECT
    t.test_id,
    t.test_name,
    t.test_cat,
    MAX(t.test_sn),
    t.result
FROM
    car_test t
GROUP BY
    t.test_name, t.test_cat

But heres what I get:

TEST_ID     TEST_NAME   TEST_CAT    TEST_SN     RESULT
1           test_a      Basic       223344      Pass
2           test_b      Basic       223344      Pass
3           test_c      Basic       223344      Pass
4           test_d      Basic       223344      Fail
5           test_e      Basic       112233      Fail
10          test_w      Advanced    112233      Fail
11          test_x      Advanced    112233      Pass
12          test_y      Advanced    112233      Pass
13          test_z      Advanced    112233      Pass

Row 5 is still present. I want to SELECT only rows that have the MAX TEST_SN value for each TEST_CAT

Any Ideas?

Answer 1

First, we write a query that gets the highest (MAX) test_sn for each category (test_cat). Like this:

 SELECT t.test_cat
      , MAX(t.test_sn)  AS max_test_sn
   FROM car_test t
  GROUP BY t.test_cat

Then, we can use that query as an inline view in another query. (The query above returns a resultset. We can reference that in place of a table name.) As a simple demonstration:

 SELECT s.test_cat
      , s.max_test_sn 
   FROM ( SELECT t.test_cat
               , MAX(t.test_sn)  AS max_test_sn
            FROM car_test t
           GROUP BY t.test_cat
           ORDER BY t.test_cat
        ) s
  ORDER BY s.test_cat

Now, we can add a JOIN to the original table, to retrieve only rows that match a row returned by the inline view query. For example:

 SELECT r.test_id
      , r.test_name
      , r.test_cat
      , r.test_sn
      , r.result
   FROM ( SELECT t.test_cat
               , MAX(t.test_sn)  AS max_test_sn
            FROM car_test t
           GROUP BY t.test_cat
           ORDER BY t.test_cat
        ) s
   JOIN car_test r
     ON r.test_cat = s.test_cat
    AND r.test_sn  = s.max_test_sn
  ORDER
     BY r.test_id

Answer 2

You should not use test_name in your group by clause. As putting test_name there makes a valid group for test-e & Basic for which the max is 112233. Did you try keeping only "test_cat" in your group by clause?