SQL Count Syntax

[Guest]

I'm building a top 10 leaderboard for contest entries and am having some trouble with the sorting on my query. My Query is almost working perfectly, however it doesn't seem to be ordering by my new variable, 'entries'. Am I missing something somewhere, or is my syntax correct? (it doesn't display at all unless I put the single quotes on the variable entries).

<cfquery name="entry_count" datasource="clients">
select reg_id, count(reg_id) as entries
from targus_promo_entry
group by reg_id
order by 'entries' DESC
</cfquery>

Does anyone have any ideas???

Allison

[cf_dev2]

Hi Allison,

Sql syntax varies by database so its good to post which database/version you're using.

Try removing the single quotes around 'entries'

select reg_id, count(reg_id) as entries
from targus_promo_entry
group by reg_id
order by entries DESC

But some databases don't allow you to order by an alias, so you'd have to use something like

select reg_id, count(reg_id) as entries
from targus_promo_entry
group by reg_id
order by count(reg_id) DESC

[Guest]

Thank you so much! The latter of the two worked. I'm using [shhhhhhhhh, don't tell everyone] Access 2007, but saved as 2000 to try and solve this issue. I'll see if it works in current format too, some things done.

Very gracious for your speedy response. :)

Allison

[cf_dev2]

> [shhhhhhhhh, don't tell everyone]

Mums the word ;-)