I have a table that reads like the following:

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?

Million thanks in advance...

if you used a view for the query which generated that output; you could do this;
Is this what you want? It should return 3 if used on that set of data above.

Not exactly... I would like the result set to be of the format:
Still struggling... any help is very highly appreciated...

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;

Tony,

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

I looked at SQL Server Books Online and can't quite find much about this "(...)" notation.

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

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;

Beautiful!!!!!!!!! You are a heck of a programmer!!!!! THANKS!!!!!