Subject | Re: [firebird-support] SELECT MIN |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2007-04-24T11:19:03Z |

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:

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