MySQL GROUP BY MAX two columns -

so ive got number of tests in database, each test belongs category , each "group" of tests has "serial number" (timestamp of sorts). each test has pass or fail result, 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 craft query return rows each category test_sn highest. result im trying return 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 test_sns "112233" , "223344" "basic", want ignore tests earlier (smaller) test_sn (rows 1,2,3,4 , 5). since "112233" test_sn advacned, want keep of rows (10,11,12 , 13).

i can come close query:

select     t.test_id,     t.test_name,     t.test_cat,     max(t.test_sn),     t.result     car_test t group     t.test_name, t.test_cat 

but heres 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 still present. want select rows have max test_sn value each test_cat

any ideas?

first, write query gets highest (max) test_sn each category (test_cat). this:

 select t.test_cat       , max(t.test_sn)  max_test_sn    car_test t   group t.test_cat 

then, can use query inline view in query. (the query above returns resultset. can reference in place of table name.) simple demonstration:

 select s.test_cat       , s.max_test_sn     ( select t.test_cat                , max(t.test_sn)  max_test_sn             car_test t            group t.test_cat            order t.test_cat         ) s   order s.test_cat 

now, can add join original table, retrieve rows match row returned inline view query. example:

 select r.test_id       , r.test_name       , r.test_cat       , r.test_sn       , r.result    ( select t.test_cat                , max(t.test_sn)  max_test_sn             car_test t            group t.test_cat            order t.test_cat         ) s    join car_test r      on r.test_cat = s.test_cat     , r.test_sn  = s.max_test_sn   order      r.test_id