>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 * from tests t1 ;
	where ;
	score = (select min(score) from tests t2 where t1.student = t2.student) ;
	or ;
	score = (select max(score) from tests t2 where t1.student = t2.student)

* Not in older versions
select t1.* from tests t1 ;
	INNER join ;
	(select student, max(score) as maxScore, min(score) as minScore ;
	from tests group by 1) t2 ;
	on t1.student = t2.student and (t1.score = t2.maxScore or t1.score = t2.minScore)

Cetin