Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: Max function problem

    Hello Everyone,

    I hope you have help me with what I'm sure is a simple fix (Beginner Alert ha)

    I've got the following query

    SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category
    FROM
    (SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
    FROM Categories
    GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories
    GROUP BY CountCategories.phoneNumber, CountCategories.category;

    As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.

    It isn't showing me the unique result for the highest count.

    As I said, I'm sure it's a simple fix ha.

    Thank you very much

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this:
    Code:
    SELECT TOP 1 *
       FROM (SELECT Categories.phoneNumber
    ,     Categories.category
    ,     Count(Categories.category) AS CountOfcategory
          FROM Categories
          GROUP BY
             Categories.category
    ,        Categories.phoneNumber
          ORDER BY
             Count(Categories.category) DESC
    ,        Categories.category
    ,        Categories.phoneNumber
          ) AS CountCategories;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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