Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Angry Unanswered: Can't ORDER BY in Access Group By query

    It should be a simple query I've done it on SQL Server db's before but it doesn't work for Access. All I need is the top 10 first names that occur with a last name of X. I am unable to order by the 'itCount' variable so I don't get the most common names. Here is my query hopefully you can help me. Thanks, Luke

    SELECT TOP 10 t1.sFName AS sTemp, (SELECT count(*) FROM tblData t2 WHERE t1.sFName = t2.sFName And t2.sSurname Like 'H*') AS itCount
    FROM tblData AS t1
    WHERE 0=0 And t1.sSurname Like 'H*'
    GROUP BY t1.sFName;
    '''I would like to add 'ORDER BY itCount' here but it throws an error

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i believe you are getting the error because in your query, itCount is an alias for the subquery, i.e. derived table

    could be wrong; wouldn't be the first time

    the GROUP BY seems unnecessary in that construction, too

    luckily your query can be rewritten in a more straightforward manner

    you'll want to sort descending, too
    Code:
    select top 10 
           sFName    as sTemp
         , count(*)  as itCount
      from tblData 
     where sSurname like 'H*'
    group 
        by sFName
    order
        by itCount desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Thumbs up

    d'oh, I guess I did overcomplicate that. Your query works with one small modification. You can't Order By an alias so you have to change the clause to "order by count(*) desc"

    Thanks again

    select top 10
    sFName as sTemp
    , count(*) as itCount
    from tblData
    where sSurname like 'H*'
    group
    by sFName
    order
    by count(*) desc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •