SQL Query help - Condition applies only for the Newest Record

Below is the simplified version of the Table. My query is select all the patientIds where the last X Test Result is over10. Result should be only patientID 1 and 3.

There are over 30k records in this table. I couldn't figure out a better way to get this done.

PatientId   Test    Result  Date
1           X       11      2011/05/11
1           X        5      2005/05/11
1           Y        5      2011/05/11
2           X        5      2011/05/11
2           X       12      2005/05/11
2           Z       12      2011/05/11
3           X       16      201开发者_JAVA技巧1/05/11
4           X       9       2005/05/11

I would do something like this:

SELECT a.PatientId
FROM
    some_table AS a
    INNER JOIN (
        SELECT PatientId, MAX(Date) AS Date
        FROM some_table
        WHERE Test = 'X'
        GROUP BY PatientId
    ) AS lr ON a.PatientId = lr.PatientId AND a.Date = lr.Date
WHERE a.Test = 'X' AND a.Result > 10

You could probably use CTEs but I dont know them by heart so I will try a traditional inner join. Disclaimer:- totally from memory so may not run.

SELECT a.* 
FROM Tests a 
INNER JOIN (SELECT patient_id, test, max(date) as maxdate 
              FROM Tests 
             WHERE test = 'X' 
          GROUP BY patient_id, test) b
        ON a.patient_id = b.patient_id
       AND a.test = b.test
       AND a.date = b.maxdate
     WHERE a.result > 10

Hope this helps.

If I understood correctly (with the data you gave us, the query should return PatientId 2 and 3), something like this should work:

WITH TT (PatientId, Test, Result, N) AS (
    SELECT PatientId,
           Test,
           Result
           ROW_NUMBER() OVER(PARTITION BY PatientId ORDER BY Date ASC)
      FROM some_table
     WHERE Test = 'X')
    SELECT PatientId
      FROM TT
     WHERE Result > 10
       AND N = 1;

Without CTE:

SELECT PatientId
  FROM (SELECT PatientId,
               Test,
               Result
               ROW_NUMBER() OVER(PARTITION BY PatientId ORDER BY Date ASC) N
          FROM some_table
         WHERE Test = 'X') tmp
 WHERE Result > 10
   AND N = 1