1

Topic: MAX on COUNT

Hello!
Question: whether it is possible somehow for one stopping to receive value, the maximum number of times entering into sampling? For example, we have sampling
4
4
4
4
4
4
4
36
36
36
36
4
4
4
5
5
5
On an output should receive: 4.
In two knee-bends it is clear that it is necessary to group at first (COUNT), then to take maximum COUNT. And for one knee-bend it is possible?

2

Re: MAX on COUNT

Maxim Kauntov;
And write:..... max (count ())......... group by <field name>

3

Re: MAX on COUNT

Maxim Kauntov;
And, a bit another is necessary to you....
Then row_number.

4

Re: MAX on COUNT

  - Eh wrote:

Maxim Kauntov;
And, a bit another is necessary to you....
Then row_number.

You wanted to tell keep?

5

Re: MAX on COUNT

Kind  - Eh;
Or through keep dense_rank first | last

6

Re: MAX on COUNT

SQL> select max (owner) keep (dense_rank first order by count (*) desc) from all_objects group by owner;
MAX (OWNER) KEEP (DENSE_RANKFIRST
------------------------------
SYS

7

Re: MAX on COUNT

Maxim Kauntov;

with t as (
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 3 as num from dual union all
select 3 as num from dual union all
select 3 as num from dual union all
select 3 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 4 as num from dual union all
select 5 as num from dual union all
select 5 as num from dual union all
select 5 as num from dual)
--
--
select max (count (num)) max_cnt
, max (num) keep (dense_rank first order by count (1) desc) x_num from t
group by num

8

Re: MAX on COUNT

Kauntov wrote:

to receive value, the maximum number of times entering into sampling? For example, we have sampling
4
4
36
36
5

And in such variant what it is necessary to receive?

9

Re: MAX on COUNT

Elic wrote:

SQL> select max (owner) keep (dense_rank first order by count (*) desc) from all_objects group by owner;
MAX (OWNER) KEEP (DENSE_RANKFIRST
------------------------------
SYS

Many thanks, ideal variant) My darkness did not allow count () in a phrase order by to put.

10

Re: MAX on COUNT

Maxim Kauntov;
Apply analytic function count (column) over (partition by a column) and you receive the second column with value "how many entrances of the given number in all sampling". Further understand

11

Re: MAX on COUNT

Arsenyev wrote:

it is passed...
And in such variant what it is necessary to receive?

In my case of the big importance is not present, it is possible and 36. I received the answer - max (id) keep (dense_rank last order by count (*))
Many thanks, topic can be closed)