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

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 i开发者_JAVA百科d 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!

---

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*/