SQL Server 2008: Filtering Out Duplicate Rows Based on Multiple Columns

Question

Hi,

Thanks to everyone who helped me with my last question. This is a similar question, but now I have a better idea of what I want. Again I'm using MS SQL Server 2008, and I'm trying to figure out a query that is a little beyond my week of SQL experience.

Right now I have the following simple query

SELECT pl.val, pl.txt_val, pl.id
FROM dm.labs pl
  WHERE pl.lab_nm = 'CK (CPK)' 
    AND pl.val < 999999;
    AND pl.val IS NOT NULL
ORDER BY pl.val;

In this table, each row corresponds to the results of a patient's lab value. The problem is that for some patients, there are rows that are actually multiple copies of the same lab reading. There is a column pl.lab_filed_dts (datetime data type) giving the date the lab was taken.

What I want to do is:

Edit: if two (or more) rows have the same id, the same val, and the same lab_filed_dts, regardless of their values in any other column, I want to return val, txt_val, and id from only one of the rows (it doesn't matter which one).

Edit: if two rows have the same id, the same val, and the same day portion (but not necessarily time) of lab_filed_dts AND the time portion of lab_filed_dts for one of them is midnight, I want to return val, txt_val, and id from the row whose lab_filed_dts time is not midnight.

Thanks again for everyone's help!

Answer 1

If I understand your question correctly:

SELECT pl.val, pl.txt_val, pl.id
FROM dm.labs pl
  WHERE pl.lab_nm = 'CK (CPK)' 
    AND pl.val < 999999;
    AND pl.val IS NOT NULL
GROUP BY  pl.val, pl.txt_val, pl.id, cast(lab_filed_dts as date)
HAVING lab_filed_dts =  max(lab_filed_dts)
ORDER BY pl.val;

/* changed pl.vak to pl.val as you can't order on a column not in the select and I assumed it was just a type*/

Answer 2

A different approach, using the lab_id mentioned later.

SELECT DISTINCT pl.val, pl.txt_val, pl.id
FROM dm.labs pl
  WHERE pl.lab_nm = 'CK (CPK)' 
    AND pl.val < 999999;
    AND pl.val IS NOT NULL
    AND lab_id NOT IN (SELECT midnight.lab_id 
                       FROM dm.labs midnight 
                             INNER JOIN dm.labs nm
                               ON  midnight.val     = nm.val AND 
                                   midnight.id      = nm.id AND 
                                   midnight.lab_id <> nm.lab_id
                       WHERE midnight.lab_filed_dts = CAST(CAST(midnight.lab_filed_dts AS DATE) 
                                                     AS DateTime)

ORDER BY pl.val;