Re: Help with select statement
From: Aram Meguerian <aram_at_unisys.com.br>
Date: 1997/05/26
Message-ID: <01bc6977$b7a06c60$4c04dcc8_at_psaphos>#1/1
Date: 1997/05/26
Message-ID: <01bc6977$b7a06c60$4c04dcc8_at_psaphos>#1/1
Have you tried to count (distinct TV#), as in:
select test_type, count (distinct TV#)
from test
group by test_type;
or
select test_type, count (TV#)
from ( select distinct test_type, TV# from test ) teste_
group by test_type;
Hope it has been useful.
--
Aram Meguerian
aram_at_unisys.com.br
-------------------------------------------------------------------
TANSTAAFL - There ain't no such thing as a free lunch
by Robert A. Heinlein
-------------------------------------------------------------------
I don't work at Unisys, it is just my Internet Provider,
so don't blame it for anything I have just said.
-------------------------------------------------------------------
Lee Parsons <lparsons_at_eskimo.com> escreveu no artigo
<EAKLx1.Jy7_at_eskimo.com>...
>
> 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
>
Received on Mon May 26 1997 - 00:00:00 CEST