>>Do you mean
>>

>>select Temp.NoThread, count(*) over (partition by Temp.NoThread) as CountThreads
>> from (SELECT Top 10 NoThread FROM Thread ORDER BY Numero DESC) Temp

>
>This returns 1 for all records.
>
>If you look at the main page, there is a top 10 for the most recent threads. Until tonight, I was doing a main SQL to collect the most recent 10 threads. Then, in the loop, for the HTML table generation, which is a For/Next 10 times, I was doing another SQL to collect the amount of messages in each thread. Tonight, I tried to simplify that by doing it all in the main SQL and avoid doing another SQL for each of those 10 records that I process in the loop. However, I cannot put the COUNT(*) in the inner SQL as this count would only apply on the WHERE clause of that inner SQL and this wouldn't be good as it wouldn't cover the entire table. So, I have to find a way to get the count once I have found the 10 records I need to deal with. And, this can only be done at the external level such as you mentioned in your message. We just need to find the right syntax.

Ok, here are ways of getting what you want:

select Temp.NoThread, MessagesPerThread
 from (SELECT Top 10 NoThread FROM Thread ORDER BY Numero DESC) Temp inner join
(select NoThread, count(*) as MessagesPerThread from Thread group by NoThread) CountMessages
on Temp.NoThread = CountMessages.NoThread

Or

;with cte_RecentThreads as (select top 10 NoThread from Thread Order by Numero Desc)
select C.NoThread, count(*) from Thread inner join cte_RecentThreads C on C.NoThread = Thread.NoThread

or alternatively using a subquery

select top 10 NoThread, (select count(*) from Thread T 
where T.NoThread = Thread.NoThread) Cnt from Thread order by Numero DESC

And also LINQ to SQL can do this easily as well.

BTW, why did you need derived table at all in your original query?