Most frequent values in a table

[Welshbird]

I have searched around for this, and it appears that Oracle does not have a built in function to do MODE (i.e. the most frequently occuring values in a table).

The only thread I can find is this one:

thread186-551184

but the reply isn;t helping me either.

Does anyone have any cunning ideas? I've started to ahve a play around with it, but I'm reasonably convinced that someone else could solve it faster or mor elegantly.

Any ideas chaps?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[pete91z]

would something like this help?

select * from (select distinct <col>, count(<col>) as counted from <table> group by <col> order by counted desc)
where ROWNUM < 2;

remove the ROWNUM to see all values

 

[Dagon]

Or use an analytic function:

select * from
(select value, rank() over (order by counter desc) rnk
from
(select value, count(*) as counter
from table
group b value))
where rnk = 1

 

[Welshbird]

Pete 91z's query seems to work (if I add a where clause to pick something at a time.

I've tried changed dagon's suggestion to add a partition but I get no value in the field at the end...

I thnk I'm being dense

This is my version of Dagon's code:

select * from
(select market, pdt, rank() over (partition by market order by counter desc) rnk
from
(select market, pdt, count(*) as counter
from sumpdt
group by market, pdt))
where rnk = 1

What am I not seeing today??

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

I should have explained that MARKET is what I need to group/partition by, and PDT is the value I'm trying t find the most frequent occurances of.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

It seems to work for me:

SQL> 
SQL> create table sumpdt (market varchar2(20), pdt varchar2(20))
  2  /

Table created.

SQL> 
SQL> insert into sumpdt values ('M1', 'A')
  2  /

1 row created.

SQL> 
SQL> insert into sumpdt values ('M1', 'A')
  2  /

1 row created.

SQL> 
SQL> insert into sumpdt values ('M1', 'z')
  2  /

1 row created.

SQL> 
SQL> insert into sumpdt values ('M2', 'B')
  2  /

1 row created.

SQL> 
SQL> insert into sumpdt values ('M2', 'C')
  2  /

1 row created.

SQL> 
SQL> insert into sumpdt values ('M2', 'C')
  2  /

1 row created.

SQL> 
SQL> select * from
  2  (select market, pdt, rank() over (partition by market order by counter desc) rnk
  3  from
  4  (select market, pdt, count(*) as counter
  5  from sumpdt
  6  group by market, pdt))
  7  where rnk = 1
  8  /

MARKET               PDT                         RNK
-------------------- -------------------- ----------
M1                   A                             1
M2                   C                             1

 

[Welshbird]

I still get a blank in the PDT column - is that because these are numbers?

I have rounded the numbers to specified decimal places, but still getting blanks....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Is it because there are null values in the PDT column and, in some cases, that is the most frequent value ?

 

[Welshbird]

there aren't any nulls, so can't be that.

I think its more likely to be me!

I'll have another play this afternoon and see what happens.

Really do appreciate all suggestions though!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]