COUNT and TOP

I want to do something like this:

SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott'

What I want is to return the number of affected rows where the column 'User' equals 'Scott'...But is should only check in the 10 latest inserted rows....

Hope you understand what I mean...

when I want to do things like that especially if performance isnt completly critical, I just take the easier to read approach and do a sub query.

Select Count(*)
FROM ( Select Top(10) * From... ) As MyAlias

That way you know for sure things will be working out as you are thinking them.|||

Hi Tigers21,

Use the following: -

SELECT COUNT(*) FROM MyTable WHERE MyTableUniqueFieldID IN
(
SELECT TOP 10 MyTableUniqueFieldID FROM MyTable
ORDER BY [DATE] DESC
)
AND [User] = 'scott'

Substitute MyTableUniqueFieldID with the primary key field of the MyTable table.

Kind regards

Scotty

|||

Thanks for your answers!...

Both ways seems to work correct for me, but which one is best for the performance?

|||

Shados has the better solution.