Finding the earliest date and then get the relevant value of another column

[inverted]

I want to make a query that returns the earliest lab result(which is a row in the table with the earliest date), the serial # and the value for test id1,2 and 3.

Sample data would look like:


This means that a patient could have many rows (because he can take many labs on diff dates) but there is a unique identifier for every lab result on a different date.
Thus for the same patient on the same date could be only one row.
For a patient could be many rows with diff dates.

PAT_SERIAL_NO LAB_DATA_DATE_ID LAB_DATE TEST_ID_1 TEST_ID_2

123345		11223332423123222	17/4/1990	45.3	23.2
123345		33242345634675743	21/7/2001	20.1	11.1
543232		43546547567487686	17/4/1990	45.3

So for this sample data the required output would be:

PAT_SERIAL_NO LAB_DATA_DATE_ID LAB_DATE TEST_ID_1 TEST_ID_2

123345		11223332423123222	17/4/1990	45.3	23.2
543232		43546547567487686	17/4/1990	45.3

I attached the table for further info.

[pbaldy]

This may help:

http://www.baldyweb.com/LastValue.htm

but your various test fields look like a normalization mistake:

Fundamentals of Relational Database Design -- r937.com

[inverted]

This may help:

http://www.baldyweb.com/LastValue.htm

but your various test fields look like a normalization mistake:

Fundamentals of Relational Database Design -- r937.com

Thank you very much.
It works great.
Now, in case I also want to add the condition of making sure that this min_lab_test im getting doesn't have a null value on TEST_ID_1.
In another words, I want to get the earliest date where the value for TEST_ID_1 is not Null.

[inverted]

Thank you very much.
It works great.
Now, in case I also want to add the condition of making sure that this min_lab_test im getting doesn't have a null value on TEST_ID_1.
In another words, I want to get the earliest date where the value for TEST_ID_1 is not Null.

[pbaldy]

You can add that criteria to the first query, but I want to reiterate that the design is not normalized.

[inverted]

You can add that criteria to the first query, but I want to reiterate that the design is not normalized.

Yeah I agree with you.
How would you redesign the table? How would you separate it?

[pbaldy]

Did you read the second link in post 2? In my view, your test ID's are no different than invoice line items, so should be records in a related table.

[inverted]

Yes thank you very much, helped me a lot.

[pbaldy]

No problem, glad it helped.