3 Replies Latest reply on Dec 5, 2007 8:56 PM by cf_dev2

    SQL Count Syntax

    Coonster2007
      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
        • 1. Re: SQL Count Syntax
          cf_dev2 Level 1
          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
          • 2. Re: SQL Count Syntax
            Coonster2007 Level 1
            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
            • 3. Re: SQL Count Syntax
              cf_dev2 Level 1
              > [shhhhhhhhh, don't tell everyone]

              Mums the word ;-)