Help with select statement

[Quoted] [Quoted] [Quoted] 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 [Quoted] of tests that have been run against the TVs. Some of the tests may have been [Quoted] run against the TV more than once. The table and data look like:

TEST_TYPE              SEQUENCE        TV#                                      
-------------------- ---------- ----------                                      
BURNIN                        1          1                                      
BURNIN                        2          2                                      
[Quoted] HIGH_VOLTAGE                  3          1                                      
[Quoted] HIGH_VOLTAGE                  4          2                                      
[Quoted] HIGH_VOLTAGE                  5          2                                      
[Quoted] POWER_OFF                     6          1                                      
[Quoted] POWER_OFF                     7          1                                      

[Quoted] 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                                                 

[Quoted] This is of course the correct answer but is a very expensive way of getting it [Quoted] since the sub-query forces an additional table scan of a large table. The real [Quoted] problem is that I'm not seeing a better way of doing it. I have some control [Quoted] of the table structure and could make some minor changes to the data but must [Quoted] keep the solution in the form of a sql statment. (preferably a single statement) [Quoted] I have simplified the data set and query but what you see is basically what [Quoted] there is to work with.

Is there a less brain-dead way of getting this answer that doesn't involve [Quoted] the hideous max of the seqence? I'm just not finding it...

Thanks,
Lee

-- 
Regards, 

Lee E. Parsons                  		lparsons_at_eskimo.com