using count with top

hey gang.

i am trying to pull a count from the top 16 records in a db.

select count(top 16 (username)) as cnt2 from bracketdb_"&tourney_id&" where
username <> 'BYE' order by POS

this is what i need to do, however, it doesn't work. i am getting a systax
error near TOP.
this is using SQLdb

any ideas?

thanks
bam

Re: using count with top

Bam wrote:
> hey gang.
>
> i am trying to pull a count from the top 16 records in a db.
>
> select count(top 16 (username)) as cnt2 from bracketdb_"&tourney_id&"
> where username <> 'BYE' order by POS
>
> this is what i need to do, however, it doesn't work. i am getting a
> systax error near TOP.

Well Duhh! :-)
Do you have Books OnLine (BOL)? If not, see:
http://www.aspfaq.com/show.asp?id=2229

> this is using SQLdb
>

I'm not clear. Is there a possibility that the count could be LESS than 16??
It will never be more than 16, right? Try this:

Select count(*) from (select top 16 username from ... ) as q

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: using count with top

"Bob Barrows [MVP]" wrote in message
news:u0bG1yLoHHA.4552@TK2MSFTNGP04.phx.gbl...
> Bam wrote:
>> hey gang.
>>
>> i am trying to pull a count from the top 16 records in a db.
>>
> I'm not clear. Is there a possibility that the count could be LESS than
> 16?? It will never be more than 16, right? Try this:
>
> Select count(*) from (select top 16 username from ... ) as q
>

yes and yes. it can be less than 16, but never more.

i used what you put there, and it seems to work fine.

Thanks once again Bob