Selecting only rows that occur a maximum number of times

I have an SQL problem and no matter what I try I can't get it to work.  Any help would be much appreciated.

We have an application that stores sampling information.

There can be many sample runs, but for each run we are only interested in samples that completed. A completed sample is indicated by the fact that it has the maximum number of tuples in the table (we expect there to be many samples with this) for a particulare run. However, different runs may have different maximums.

The table is as follows

Run integer
Sample integer
Step integer
Result integer

The data looks like this

Run Sample Step Result

 1      1      1     x
 1      1      2     x
 1      1      3     x
 1      2      1     x
 1      2      2     x
 1      2      3     x
 2      1      1     x
 2      1      2     x
 2      1      3     x
 2      2      1     x
 2      2      2     x
 2      3      1     x
 2      3      2     x
 2      3      3     x
 2      3      4     x

(15 rows)

So from the above,

SELECT Run, Sample, COUNT(*)
FROM table
GROUP BY Run, Sample

gives

Run Sample COUNT(*)

 1      1      3
 1      2      3
 2      1      3
 2      2      2
 2      3      4

the overall goal is to select the tuples that belong to a samples that have the same number of tuples as the sample with the maximum within the run i.e. in this example select all APART FROM run 2, sample 2

i.e. a result set of:

Run Sample Step Result

 1      1      1     x
 1      1      2     x
 1      1      3     x
 1      2      1     x
 1      2      2     x
 1      2      3     x
 2      1      1     x
 2      1      2     x
 2      1      3     x
 2      3      1     x
 2      3      2     x
 2      3      3     x
 2      3      4     x

(13 rows)

I've played around with counts, havings, maxs, etc. but to no avail.

I'm sure there must be a way to do this??

TIA Received on Fri Apr 11 2003 - 07:30:04 CDT