Well, the query gets the smallest diff for each patientid and fecha and
you are just interested in the smallest diff for each patientid. So just
delete the last line and write something like:

SELECT t.PATIENTID, t.fecha t.DIFF
FROM MYTABLE t
WHERE t.DIFF = (SELECT MIN(q.DIFF) FROM MYTABLE q
WHERE t.PATIENTID = q.PATIENTID)

or (alternatively)

SELECT t.PATIENTID, t.fecha t.DIFF
FROM MYTABLE t
WHERE not exists(SELECT * FROM MYTABLE q
WHERE q.PATIENTID = t.PATIENTID
AND q.DIFF < t.DIFF)

HTH,
Set

Alejandro Garcia wrote:

> Jajaja I certainly don't have the hang.
> =20=20=20
> It's not working.. it does not select the MIN DIFF for each patient, it s=
> hows all the patients, like if the MIN(DIFF) wasn't there, but it is:
> =20=20=20
> SELECT
> t.PATIENTID,
> t.FECHA,
> t.DIFF
> FROM MYTABLE t
> WHERE
> t.DIFF =3D (SELECT MIN(q.DIFF) FROM=20=20
> MYTABLE q
> WHERE t.PATIENTID =3D q.PATIENTID AND t.FECHA =3D q.FECHA)
> =20=20=20
> =20=20=20
> =20=20=20
> =20=20
>
> Helen Borrie <helebor@...> escribi=F3:
> At 07:12 PM 24/04/2007, you wrote:
>> Hi, I have this problem: my table is
>> PATIENTID, DATE, DIFF
>>
>> for each PATIENTID can be many DATE and many DIFF, DIFF is an=20
>> integer and I need for each PATIENTID the MIN DIFF and the DATE of=20
>> the same line where MIN(DIFF) is.
>>
>> I did this: but it's not working
>>
>> SELECT PATIENTID,DATE,DIFF FROM T MYTABLE
>> WHERE DIFF =3D (SELECT MIN(DIFF) FROM Q MYTABLE
>> WHERE (T.PATIENTID =3D Q.PATIENTID
>> AND T.DATE =3D Q.DATE )
>
> No, it wouldn't be working. I don't think you quite have the "hang"=20
> of table aliasing yet! ;-)
>
> SELECT
> t.PATIENTID,
> t.fecha
> t.,DIFF
> FROM MYTABLE t
> WHERE
> t.DIFF =3D (SELECT MIN(q.DIFF) FROM MYTABLE q
> WHERE t.PATIENTID =3D q.PATIENTID
> AND t.fecha =3D q.fecha )
>
> ./heLen