Thread: Max function problem
07-09-15, 18:18 #1Registered User
- Join Date
- Jul 2015
Unanswered: Max function problem
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
(SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
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
07-10-15, 00:45 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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;In theory, theory and practice are identical. In practice, theory and practice are unrelated.