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

(plaster1@gmail.com) writes:
> 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 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

Since you did not provide CREATE TABLE and INSERT statements with the
sample data, this is untested:

SELECT col1, col2, col3, col4
FROM (SELECT col1, col2, col3, col4,
rn = row_number() OVER(PARTITION BY col1, col2
ORDER BY col3)
FROM tbl) AS d
WHERE rn = CASE WHEN col4 IS NOT NULL THEN 1 ELSE rn END


This solution requires SQL 2005. Tip: always say which version of
SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

On Apr 5, 4:55 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (plast...@gmail.com) writes:
> > 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 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
>
> Since you did not provide CREATE TABLE and INSERT statements with the
> sample data, this is untested:
>
> SELECT col1, col2, col3, col4
> FROM (SELECT col1, col2, col3, col4,
> rn = row_number() OVER(PARTITION BY col1, col2
> ORDER BY col3)
> FROM tbl) AS d
> WHERE rn = CASE WHEN col4 IS NOT NULL THEN 1 ELSE rn END
>
> This solution requires SQL 2005. Tip: always say which version of
> SQL Server you are using.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks for the response, and sorry for the lack of info: SQL ver 7.0

-b

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).

(plaster1@gmail.com) writes:
> 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).

A common recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it easy to copy and paste to develop a tested solution. Without
that, you get more or less guessworks.

Ed's query should be OK in SQL 7, but 1) I don't know how you adapted it
to your problem 2) what error message you got.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Apr 9, 4:59 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (plast...@gmail.com) writes:
> > 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).
>
> A common recommendation is that you post:
>
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o The desired output given the sample.
>
> This makes it easy to copy and paste to develop a tested solution. Without
> that, you get more or less guessworks.
>
> Ed's query should be OK in SQL 7, but 1) I don't know how you adapted it
> to your problem 2) what error message you got.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

You're right Erland, Ed's UNION query did the trick after a slight
amount of tweeking. I'll definitely use your advice as well when
posting to the groups. Thanks to you both for your time!

-B