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
> of the table structure and could make some minor changes to the data
> but must
> keep the solution in the form of a sql statment. (preferably a single
> statement)
> I have simplified the data set and query but what you see is basically
> what
> there is to work with.
>
> Is there a less brain-dead way of getting this answer that doesn't
> involve
> the hideous max of the seqence? I'm just not finding it...
>
> Thanks,
> Lee
> --
> Regards,
>
> Lee E. Parsons lparsons_at_eskimo.com

 Try this, I think this fixes your problem:

SELECT test_type, count(DISTINCT tv#)
FROM TEST
GROUP BY test_type;

Hope this helps, please let me know if this fixes your problem or not.

Edward Nunez
Oracle Consultant
Office: (319) 375-1160
Home: (319) 396-7039 Received on Thu May 22 1997 - 00:00:00 CEST