Now it works!!! In the subquery I only force PATIENTID to be equal:

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 )



Alejandro Garcia <aleplgr@...> escribió:
Jajaja I certainly don't have the hang.

It's not working.. it does not select the MIN DIFF for each patient, it shows all the patients, like if the MIN(DIFF) wasn't there, but it is:

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 AND t.FECHA = q.FECHA)





Helen Borrie <helebor@...> escribió:
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
>integer and I need for each PATIENTID the MIN DIFF and the DATE of
>the same line where MIN(DIFF) is.
>
> I did this: but it's not working
>
> SELECT PATIENTID,DATE,DIFF FROM T MYTABLE
> WHERE DIFF = (SELECT MIN(DIFF) FROM Q MYTABLE
> WHERE (T.PATIENTID = Q.PATIENTID
> AND T.DATE = Q.DATE )

No, it wouldn't be working. I don't think you quite have the "hang"
of table aliasing yet! ;-)

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
AND t.fecha = q.fecha )

./heLen

---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

[Non-text portions of this message have been removed]






---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

[Non-text portions of this message have been removed]