#### 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?

#### Re: MAX on COUNT

Maxim Kauntov;
And write:..... max (count ())......... group by &lt;field name&gt;

#### Re: MAX on COUNT

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

#### Re: MAX on COUNT

- Eh wrote:

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

You wanted to tell keep?

#### Re: MAX on COUNT

Kind  - Eh;
Or through keep dense_rank first | last

#### Re: MAX on COUNT

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

#### 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``````

#### 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?

#### Re: MAX on COUNT

Elic wrote:

``````SQL&gt; 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.

#### 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

#### 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)