select distinct record only if certain column not null

[plaster1]

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

[Erland Sommarskog]

(pl******@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, es****@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

[Ed Murphy]

pl******@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

[plaster1]

On Apr 5, 4:55 pm, Erland Sommarskog <[e-mail]:

([e-mail]) 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, [e-mail]

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

[plaster1]

On Apr 5, 10:04 pm, Ed Murphy <[e-mail]:

[e-mail] 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).

[Erland Sommarskog]

(pl******@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, es****@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]

On Apr 9, 4:59 pm, Erland Sommarskog <[e-mail]:

([e-mail]) 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, [e-mail]

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