Re: Help with select statement

Lee Parsons wrote:
>
> And in the category of stupid sql tricks...
>
> I have a table that contains a list of TV Serial numbers, a sequence, and a list
> of tests that have been run against the TVs. Some of the tests may have been
> run against the TV more than once. The table and data look like:
>
> TEST_TYPE SEQUENCE TV#
> -------------------- ---------- ----------
> BURNIN 1 1
> BURNIN 2 2
> HIGH_VOLTAGE 3 1
> HIGH_VOLTAGE 4 2
> HIGH_VOLTAGE 5 2
> POWER_OFF 6 1
> POWER_OFF 7 1
>
> If I want to know how many different TVs have had a particular test
> run against them, I could run the following.
>
> select test_type, count(*) from test where sequence in
> (select max(sequence) from test group by test_type, tv#)
> group by test_type;
>
> and get
>
> TEST_TYPE COUNT(*)
> -------------------- ----------
> BURNIN 2
> HIGH_VOLTAGE 2
> POWER_OFF 1
>
> This is of course the correct answer but is a very expensive way of getting it
> since the sub-query forces an additional table scan of a large table. The real
> problem is that I'm not seeing a better way of doing it. I have some control
> (snip)

select test_type,count(distinct tv#) from test group by tst_type;

Chrysalis. Received on Thu May 22 1997 - 00:00:00 CDT