Hello
I am using 11G XE
I have two columns in a table
id value
1 169
1 169
2 171
2 169
3 182
4 169
2 171
1 169
1 169
1 214
5 111
1 68
I would like to know what is maximum count of any value for each 'id'
my desired output is:
id maximum occurrance of a value
1 4 ------------------- > (since 169 appears for 4 times for id 1)
2 2 ------------------- > (since 171 appears for 2 times for id 2)
3 1 ------------------- > (since 182 appears for 1 time for id 3)
4 1 ------------------- > (since 169 and 111 appear for 1 time for id 4)
Note: If there is some equality (more than one 'value' appears for equal number of times for a id then the maximum count of any of those value should come as in the case of id 4 above.
I need the shortest possible query to find the solution, since i need to insert the same in a subquery. Also I want that only 'id' column comes in 'group by' clause and not the 2nd column
I hope i have been able to make my requirment clear. thanks in advance.
Hamza
Edited by: Hamza on Jul 27, 2011 1:51 AM