CODEWITHSUNDEEP
sqlsql-server
statsguyz
statsguyz

Reputation: 469

Returning results based on last date in SQL

I'm interested in retaining only individuals in a Microsoft SQL Server query that have a value over a certain threshold on two separate dates. If the individuals have a score higher than 150 twice, I would like to return their ID, the second date that their score was above 150, and the score on the second date.

Here is the data:

SubjectID   DATE         Score
001         01/11/2014   147
001         02/11/2013   151
002         02/10/2015   152
003         08/12/2013   155
002         01/31/2012   159
003         07/19/2016   157

So I for the results, I would like to return the following:

SubjectID   DATE         Score
002         02/10/2015   152
003         07/19/2016   157

Upvotes: 0

Views: 119

Answers (4)

Jalindar
Jalindar

Reputation: 44

select sub.* 
FROM( 
    Select t.subjectId,
         t.date,
         t.score,
        , COUNT(t.score) OVER (PARTITION BY t.subjectId) as date_count
        , rank() OVER (PARTITION BY t.subjectId ORDER BY t.date desc ) as latest_date_rank
    FROM  tbl t 
    WHERE t.score >150 
) as sub 
WHERE sub.date_count >= 2 AND sub.latest_date_rank =1

I hope this will work for n no of records and will give latest dates record. Plz ignore formating as typed from handheld device.

Upvotes: -1

user4321
user4321

Reputation: 635

for id alone 

SELECT a.subjectId
FROM Clinic a
WHERE a.score > 150 
      AND a.date IN (SELECT MAX(b.date) 
                     FROM Clinic b 
                     WHERE b.subjectId = a.subjectId 
                            AND b.score > 150)

for all fields

 SELECT *
    FROM Clinic a
    WHERE a.score > 150 
          AND a.date IN (SELECT MAX(b.date) 
                         FROM Clinic b 
                         WHERE b.subjectId = a.subjectId 
                                AND b.score > 150)

Upvotes: 1

Adilson de Almeida Jr
Adilson de Almeida Jr

Reputation: 2755

You can use Rank..Over

select * from 
(
   select *, RANK() OVER( PARTITION BY SubjectID ORDER BY [Date]) Rnk
   from [YourTable]
   where Score > 150
) x
where Rnk = 2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want anything beyond the first, then one method is a correlated subquery:

select t.*
from t
where t.score > 150 and
      t.date > (select min(t2.date) from t t2 where t2.subjectId = t.subjectId and t2.score > 150)

If you want only the maximum such date:

select t.*
from t
where t.score > 150 and
      t.date = (select max(t2.date) from t t2 where t2.subjectId = t.subjectId and t2.score > 150)

Upvotes: 2

Related Questions