How to count row numbers

[henryz]

Hi,

For example I have a simple table called Session like this:

PersonID SessionNum
========== =============
101 105
101 21
102 33
102 46
102 57
103 69
. .
. .
The SessionNum are unique, but in a random order.
Can someone suggest how to write a query, to find which person has the most occurance of the SessionNum, and how many? and which person has the less?

Thanks in advance,
-----
Henry

 

[LKBrwnDBA]

Try using the MAX(), MIN() aggregate functions.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Henry,

Here is some code, using your posted values:

col a heading “Person|at|Max/Min” format 999
col b heading “Max/Min|Sessions”
select Pers a, MaxMin_sess b
from (Select max(count(*)) MaxMin_sess from sess group by personid
      union
      Select min(count(*)) from sess group by personid) a
    ,(select personid pers,count(*) pers_cnt from sess
       group by personid) b
where b.pers_cnt = MaxMin_sess
/

 Person
     at    Max/Min
Max/Min   Sessions
------- ----------
    103          1
    102          3

Let us know if this approaches what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[henryz]

Thanks Mufasa.

This solution looks pretty good, but our database is reasonable large, with over a million records. Tried this on our database, it just timed out without returning any results.

Appreciate your help anyway.

 

[SantaMufasa]

Henry,

Timeouts don't occur from lack of results reaching the screen...they happen from lack of activity. Reading millions of records is not lack of activity. IMHO, if you received a timeout, I believe there is another reason.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.