Posted by plaster1 on 04/09/07 20:33

On Apr 5, 10:04 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> plast...@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- Hide quoted text -
>
> - Show quoted text -

Thanks Ed. Although i'm getting syntax issues with the UNION portion
of you query. Perhaps that's a version issue as well (i'm using SQL
server 7.0).

[Back to original message]