SELECT * FROM mytable mt1 WHERE testname = "math" AND score = (SELECT MAX(score) FROM mytable WHERE testname = mt1.testname and student# = mt1.student#) UNION ALL SELECT * FROM mytable mt1 WHERE testname = "chem" AND score = (SELECT MIN(score) FROM mytable WHERE testname = mt1.testname and student# = mt1.student#)>
>date student# name testname score > >070401 11111 joeblow math 77 >070402 11111 joeblow math 88 >070403 11111 joeblow chem 0 > >070401 22222 johnfit math 99 >070402 22222 johnfit math 88 >070403 22222 johnfit chem 66 >070404 22222 johnfit chem 0>
>070402 11111 joeblow math 88 >070403 11111 joeblow chem 0 > > >070401 22222 johnfit math 99 >070404 22222 johnfit chem 0>