Posted by plaster1 on 04/05/07 19:23

Been trying to come up with a query to filter-down my sample set into
distinct records. For instance, lets say column1 is a sample set,
column2 is the parameter, and column3 is a name and column4 is a type
(lets also say there is a fifth column (id) that is an id). What I
need is one record per type per sample only if type is given, if not,
then return that record as well.

I've used a subquery to get as close to the desired query is as
possible:

select * from table1
where id in (select min(id) from table1
where column1="A"
group by column1, column2)

Here's an example of all data for sample "A":

1 2 3 4
----------
A 1 X P
A 1 Y P
A 1 Z P
A 2 W
A 3 W
A 4 T P
A 5 U P
A 6 V P
A 7 T
A 7 U
A 7 V

I want output :

1 2 3
-------
A 1 X P
A 2 W
A 3 W
A 4 T P
A 5 U P
A 6 V P
A 7 T
A 7 U
A 7 V

Except the above query will exclude the last two records because
column3 is not 'grouped by'.

Basically I need to reduce any 'range' of records per sample (column
a) where column4 is not null (ie = 'P'), to only one record and
keeping all others. Thanks in advance:

-B

[Back to original message]