Select Top 2 by group 1

[JamesDSM50328]

I'm needing some help with this query/subquery to select the top 2 from each group

SELECT T1.[First Name], T1.[Last Name], T2.[Record Type], T2.TimeStamp
FROM T2 AS B1, T1 INNER JOIN T2 ON T1.ID = T2.ProviderID
WHERE (((B1.TimeStamp)=(Select Top 2  B1.TimeStamp from B1 Where B1.[ProviderID]=[T2].[ProviderID] ORDER BY B1.TimeStamp DESC)));

Error that is coming up is "...cannot find input table or query 'b1'..."

If anyone can lend some assistance with this.

 

[PHV]

Perhaps this ?

SELECT T1.[First Name], T1.[Last Name], T2.[Record Type], T2.TimeStamp
FROM T1 INNER JOIN T2 ON T1.ID = T2.ProviderID
WHERE T2.TimeStamp In (Select Top 2 B1.TimeStamp from T2 As B1 Where B1.ProviderID=T2.ProviderID ORDER BY B1.TimeStamp DESC);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JamesDSM50328]

Thanks that works perfectly, I see where and understand where I was going wrong now. I really appreciate it.