Try (not tested)

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#)

>
>I have a table with 5 fields:
>
>mytable
>

>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

>
>to have a new table
>
>mynewtable with higher score in math and lower score in chem=0 only.
>

>070402 11111 joeblow math 88
>070403 11111 joeblow chem 0
>
>
>070401 22222 johnfit math 99
>070404 22222 johnfit chem 0

>
>Please assist me with SQL coding would be appreciated very much.
>
>TK in advance.