Using SOQL aggregate functions

Question

I have a table in salesforce that stores the topic selected by a person and a person may select the same topic multiple times. At a high level this how the data is stored.

{"Person1, "Topic1", "Person1", "Topic1", "Person1", "Topic2", "Person2", "Topic3", "Person2", "Topic1", "Person2", "Topic3"}

I want to write a SOQL query that returns the person and the most frequently selected topic. For the above example the results will be like this:

 {"Person1", "Topic1", "Person2", "Topic3"}

I tried to use aggregate queries using MAX and Count but was not successful. Can any one tell me if its even possible with SOQL. Write now the only work around I am using is to dump this data in a map and looping through it to generate the desired output.

Answer 1

You can write SOQL like this:

Select Person , Topic, count(Topic) CNT from TABLE group by Person , Topic order by  Person , Topic,  CNT DESC

This will return all topic counts selected by each person order by CNT

Then you can apply logic to fetch top row (which has max count) for each person , for each topic.