SQL Count

[MarkLegg87]

Im trying to do a COUNT query using SQL within ASP. This is being used for a forum I am creating. The count is to get the number of threads and posts within a discussion topic from a database. The code I have so far is:

objcommand.commandtext = "SELECT COUNT (thread_id) FROM ForumThread_Headings WHERE Discussion_ID=" & var_id & "ORDER BY Thread_ID"

Thanks for any help i receive

[chazzy]

Ok... do you have a question?

[MarkLegg87]

yes why doesnt that work, and any suggestions of how i can make it work

[chazzy]

do you get an error? what is the error? the only thing i see is maybe add a space before the order by clause

[Nedals]

...and if you only want a count, you don't need an ORDER BY. It just slows things down

[chazzy]

...and if you only want a count, you don't need an ORDER BY. It just slows things down

Oh? Is this based on mysql or something? Because the OP hasn't specified what DBMS. Also, if you're going for the old mysql explanation it's easily fixed by increasing the size of the sort_buffer.

[Nedals]

That's a strange response. :)
If all you want is a count, why do you need to order the result regardless of the DBMS?

[chazzy]

i'm not arguing your point of it being irrelevant, but i am arguing your point that it's slower (besides, if mysql had a good sql compiler, it would drop the order by since it can tell there will be only one row returned)