1. Registered User
Join Date
Feb 2003
Posts
15

I have a table that reads like the following:

Code:
```--ST-- | --DOK-- | --Occurence--
1 |          1 |                 6
2 |          2 |                 5
3 |          1 |                 3
3 |          2 |                 3
3 |          3 |                 1```
It means the following:
- In ST (aka Item) 1, its depth-of-knowledge (DOK) contains only 1's, and it has occured 6 times (ie. someone rated [Item 1] six times with 1)
- In Item 2, someone rated it five times with 2
- In Item 3, someone rated it altogether seven times, with values from 1 through 3. The distribution is stated in the Occurence column.

I want to find the statistical MODE (most frequently appeared) value of the DOK for each ST (aka Item). In case of a tie, I want to choose the max of all the ties (In Item 3, it will be '2' that gets chosen instead of '1')

I am unable to write up my SELECT statement after trying and trying for 2 days... can someone with extensive SQL experiences help me with this statistics MODE problem?

2. Super Moderator
Join Date
Feb 2002
Location
San Francisco, CA
Posts
441
if you used a view for the query which generated that output; you could do this;
Code:
```select max(st)
from (select st,count(*) from VIEW_OF_ST group by st order by st)
where rownum<2```
Is this what you want? It should return 3 if used on that set of data above.

3. Registered User
Join Date
Feb 2003
Posts
15
Not exactly... I would like the result set to be of the format:
Code:
```--ST--|--DOK--
1 |       1
2 |       2
3 |       2       //For ST=3, its maximum most-frequently-appeared DOK is 2```
Still struggling... any help is very highly appreciated...

4. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Try this:

select st, max(dok)
from t
where (st, occurence) in
(
select st, max(occurence) max_occurence from t
group by st
)
group by st;

5. Registered User
Join Date
Feb 2003
Posts
15
Tony,

For some reason it tells me that there's a syntax error using the notation "WHERE (ST, Occurence) IN"

Is this notation only supported in some DB? I am using SQL Server 2000.

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Ah, I am using Oracle - maybe this syntax is non-standard then, though I had never realised that. What about "in-line views"?

select t.st, max(t.dok)
from t,
(
select st, max(occurence) max_occurence from t
group by st
) v
where t.st = v.st
and t.occurence = v.max_occurence
group by t.st;

7. Registered User
Join Date
Feb 2003
Posts
15
Beautiful!!!!!!!!! You are a heck of a programmer!!!!! THANKS!!!!!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•