|
|
Posted by Ed Murphy on 04/06/07 03:04
plaster1@gmail.com wrote:
> 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:
What about this?
select column1, column2, column3, column4
from table1
where id in (
select min(id)
from table1
group by column1, column2
)
and column4 is not null
union
select column1, column2, column3, column4
from table1
where column4 is null
What would you want to do with the following data?
1 2 3 4 id
----------
A 8 X P 12
A 8 Y Q 13
A 9 X P 14
A 9 Y 15 <- column4 is null
Navigation:
[Reply to this message]
|