Hello

I would like to get the rows with the maximum number rows of each group of fields.

For example, I have a table called "sometable", with columns "x" and "y". It looks like this:

x y
1 77
1 77
1 22
1 44
1 44
1 44
2 66
2 99
2 33
2 33

So, I'd like to do:

select x, y, count(*)
from sometable
group by x, y
having count(*) = max(count(*));

And yield a result of 2 rows:

x y count
1 44 3
2 33 2

But of course, Ingres won't let me embedd aggregate functions like that. So how can I do this? A solution in ANSI SQL would be great! Answers using Ingres-specific features are acceptable too.

Thanks in advance!

I wanna help but I am not quite clear on what you are trying to do. Do you only wnat the TOP 2 with the highest count or do you want the ones with their count being greater than a given value ?


AmcAmx

select x, y, count(*)
from sometable
group by x, y
order by col3 desc

will give you a list in descending order of the number of occurances of x and y.

Not sure if this is what you are after though?

I'd suggest:-PatP