>Hi all,
>
>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.

select mt.date, mt.student#, mt.name, mt.testname, ;
res.score from myTable mt ;
inner join (select student#, max(score) as score from myTable group by 1 where testname = 'math') res on ;
mt.student# = res.student# and mt.score = res.score and mt.testname = 'math' ;
union all ;
select mt1.date, mt1.student#, mt1.name, mt1.testname, ;
res1.score from myTable mt1 ;
inner join (select student#, min(score) as score from myTable group by 1 where testname = 'chem') res1 on ;
mt1.student# = res1.student# and mt1.score = res1.score and mt1.testname = 'chem' ;
into table myNewTable

from the top of my head (not tested). This is assuming you only have two different tests - math and chemistry.