CODEWITHSUNDEEP
mysqlsqlsubquery
Dasun Chathuradha
Dasun Chathuradha

Reputation: 13

How to construct this subquery?

I have this table in mysql - There is NO ANY KEYS defined.

        Real Name      : DB Field   :    Type    |
-----------------------|------------|------------|
       Patient ID      :   pid      : varchar50  |
White Blood Cell Count :   wbc      : int        |
        Lab Date       :   ldate    : datetime   |
-------------------------------------------------|

Each Patient has 10 laboratory tests done. These tests done in 10 different days.So for one patient, there are 10 rows (with same pid) in the table with 10 'wbc' values and their laboratory test dates. In the above table I want to get the corresponding date for the Minimum 'wbc' count grouped by 'pid'

select pid, min(wbc),ldate from tbl_temp group by pid

above query can get minimum wbc but not the corresponding date in the same row. It only gets the first value of the date column for the relevant pid.

Please help me to write a combined query for this.

Upvotes: 0

Views: 91

Answers (2)

DRapp
DRapp

Reputation: 48139

Let me try to restate... For each patient, you want the minimum white blood cell count... And from that lowest white blood cell count, you want to know what date it happened.

Start first with the lowest count (min) grouped by patient. From that, re-join to the original table by each patient and grab the date that has that lowest value.

select
      yt2.pid,
      yt2.wbc,
      yt2.ldate
   from
      ( select 
              yt1.pid,
              min( yt1.wbc ) LowestWBCValue
           from
              YourTable yt1
           group by
              yt1.pid ) PreQuery
         Join YourTable yt2
            on PreQuery.pid = yt2.pid
           AND PreQuery.LowestWBCValue = yt2.wbc

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

To get data associated with a minimum value, join to a subquery. Here is the general idea

select fred, barney, mindate
from MyTable
join (
select wilma, min(datefield) mindate
from MyTable
group by Wilma ) temp on temp.Wilma = MyTable.Wilma
and datefield = mindate

You should be able to figure out how to incorporate this concept to your own situation.

Upvotes: 1

Related Questions