Re: Selecting only rows that occur a maximum number of times

From: andrewst <member14183_at_dbforums.com>
Date: Fri, 11 Apr 2003 15:22:52 +0000
Message-ID: <2756835.1050074572@dbforums.com>

Originally posted by Ed
> 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

This is making my head spin. Since run 2 sample 3 has 4 steps, why is run 2 sample 1 wanted when it only has 3 steps?

I think perhaps you want this:

SQL> select * from results where (run, sample) in   2 (
  3 select s.run, s.sample
  4 from
  5 ( select run, max(cnt) max_cnt

  6      from
  7      ( select run, sample, count(*) cnt
  8        from   results
  9        group by run, sample
 10      )
 11      group by run

 12 ) m,
 13 ( select run, sample, count(*) cnt

 14      from   results
 15      group by run, sample

 16 ) s
 17 where s.run = m.run
 18 and s.cnt = m.max_cnt
 19* /

       RUN SAMPLE STEP R
---------- ---------- ---------- -

         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          3          1 x
         2          3          2 x
         2          3          3 x
         2          3          4 x

10 rows selected.

--
Posted via http://dbforums.com

Received on Fri Apr 11 2003 - 10:22:52 CDT