TOP clause not working

[fabilewk]

I'm using this query:

SELECT Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (SELECT Stats.[userid], First(Stats.[document]) AS FirstOfDocument FROM Stats WHERE GROUP BY Stats.[userid], Stats.[document]) AS Query1
GROUP BY Stats.[userid]
ORDER BY Count(Stats.[userid]) DESC;



but what I actually need is just the top 20 userid, so I added the TOP 20 clause:

SELECT TOP 20 Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM....

but this isn't working. Any ideas?
Any help would be appreciated.

[rpeare]

SELECT Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (SELECT Stats.[userid], First(Stats.[document]) AS FirstOfDocument   FROM Stats GROUP BY Stats.[userid], Stats.[document])  AS Query1
GROUP BY Stats.[userid]
ORDER BY Count(Stats.[userid]) DESC;

I took out the word WHERE from your statement and mine worked. Not sure why you did your query this way but this is simpler and it works the same way.

SELECT TOP 20 Stats.UserID, Count(Stats.Document) AS TotalDocs
FROM Stats
GROUP BY Stats.UserID
ORDER BY Count(Stats.Document) DESC;

[fabilewk]

Excuse me, but que query would be something like this:

SELECT Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (
SELECT Stats.[userid], First(Stats.[document]) AS FirstOfDocument
FROM Stats
WHERE
(Stats.[document] LIKE '*.pdf' OR Estadísticas.[document] LIKE '*.doc' OR Stats.[document] LIKE '*.xls')
AND Stats.[document] NOT LIKE 'DocumentX' AND
Stats.[document] NOT LIKE 'DocumentY' AND
Stats.[document] NOT LIKE 'SomethingZ' AND
Stats.[userid] NOT LIKE 'somebodyX' AND
Stats.[userid] NOT LIKE 'somebodyY' AND
Stats.[userid] NOT LIKE 'somebodyZ'
GROUP BY Stats.[userid], Stats.[document]) AS Query1
GROUP BY Stats.[userid]
ORDER BY Count(Stats.[userid]) DESC;

Thanks for your help

[rpeare]

I don't understand this question if there is one.

[fabilewk]

The question is in the first post.
I'm using this query:

SELECT Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (
SELECT Stats.[userid], First(Stats.[document]) AS FirstOfDocument
FROM Stats
WHERE
(Stats.[document] LIKE '*.pdf' OR Estadísticas.[document] LIKE '*.doc' OR Stats.[document] LIKE '*.xls')
AND Stats.[document] NOT LIKE 'DocumentX' AND
Stats.[document] NOT LIKE 'DocumentY' AND
Stats.[document] NOT LIKE 'SomethingZ' AND
Stats.[userid] NOT LIKE 'somebodyX' AND
Stats.[userid] NOT LIKE 'somebodyY' AND
Stats.[userid] NOT LIKE 'somebodyZ'
GROUP BY Stats.[userid], Stats.[document]) AS Query1
GROUP BY Stats.[userid]
ORDER BY Count(Stats.[userid]) DESC;

but I need just the top 20 userid.
When I added the TOP clause

SELECT TOP 20 Stats.[userid] AS User, Count(Stats.[userid]) AS Docs
FROM (....

the query returned 32 records.

I would like to know why the top 20 clause is ignored.
Thanks

[rpeare]

I think the problem you're encountering is that the TOP X sql statement will return all ties

So let's say you have 2 matching documents for the last person on your TOP list, and there are 50 people with 2 documents it's going to show all of those because they match your lowest TOP value.

If you want to limit it to the last USERID it finds with ties then you have to modify your ORDER BY statement to include the USERID field (i.e. sort by the number of documents first, then by the USERID) Just be aware that all ties will not show up and you are always going to favor people with a lower USERID.