Big help is needed...

I have a table that reads like the following:

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

Million thanks in advance...if you used a view for the query which generated that output; you could do this;

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.|||Not exactly... I would like the result set to be of the format:

--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...|||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!!!!!