Nested select + first pass yield

[jugbugs]

I am having some trouble with forming a nested SQL query to grab the first pass yield of a given widget.  This is more of an intellectual fulfillment requirement vs it having to be done this way.

 

My table is:

create table data (sn varchar(64),an varchar(64), tracking timestamp, result boolean);

Where sn is the serial number, an is the assembly number (model), tracking is the time the entry was inserted and result is pass or fail (0 or 1 respectively).

 

The table can contain multiple entries for a given serial number (like if a widget failed three times then passed one).  I want to count the number of passes and fails per assembly number but only include the first time a given sn was seen (min(tracking)/distinct sn).  I was able to accomplish this using temporary tables, but would like to do single nested statement instead.

 

Thanks in advance for the help.

[fenway]

Don't use abbreviations for column names -- that's just confusing.

 

The lazy way is use to a use a GROUP by on assembly_number, then use MIN(tracking), and then count these.

 

Perhaps a better way is to join a table for unique assembly_number to the base table on the first record in the index spanning these fields.