Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Help with select statement
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.comReceived on Thu May 22 1997 - 00:00:00 CDT