TOP subquery

[helpaccess]

I am trying to pull the two most recent levels of care scores for each client. However, when I run this below, it returns two records total each from a different client. I would like it to return two records for each client that has two scores. Any ideas? Thank you!

SELECT LOCUSdates.[CLIENT #], LOCUSdates.[Date Completed], LOCUSdates.[Level Of Care]
FROM LOCUSdates
WHERE (((LOCUSdates.[Date Completed]) In (SELECT TOP 2 LOCUSdates.[Date Completed] FROM LOCUSdates WHERE (LOCUSdates.[CLIENT #]) = (LOCUSdates.[CLIENT #]) ORDER BY LOCUSdates.[Date Completed] Desc)) AND ((LOCUSdates.[Level Of Care]) Like "*"));

[ajetrumpet]

I don't think you'll have luck with this. one record for groups and such is fine, but top 2 or more is a stretch in one query. try writing 2 queries and in the second one, check the first one using a "NOT IN" subquery.

[helpaccess]

Thanks! Do you know how I would pull just the most recent scores for the first query then?

[ajetrumpet]

Thanks! Do you know how I would pull just the most recent scores for the first query then?

you don't. to be honest, I don't know how to do it. You can do it more than one way.

try calling a function to do it. With that, you don't need any grouping. All you would need is the TOP 2 clause and a function call with the client identifier in it.

[orange]

Check Top N at this site
http://allenbrowne.com/subquery-01.html

[helpaccess]

I figured out how to get the most recent date below. Can you elaborate on how to use NOT IN to get the next to last date? Thank you so much!

SELECT Max([SATS-R LOCUS].[Date Completed]) AS [MaxOfDate Completed], [SATS-R LOCUS].Client
FROM [SATS-R LOCUS]
GROUP BY [SATS-R LOCUS].Client
ORDER BY Max([SATS-R LOCUS].[Date Completed]) DESC;