By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

select distinct record only if certain column not null

P: n/a
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

Apr 5 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
(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
Apr 5 '07 #2

P: n/a
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
Apr 6 '07 #3

P: n/a
On Apr 5, 4:55 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
(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

Apr 9 '07 #4

P: n/a
On Apr 5, 10:04 pm, Ed Murphy <emurph...@socal.rr.comwrote:
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).

Apr 9 '07 #5

P: n/a
(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
Apr 9 '07 #6

P: n/a
On Apr 9, 4:59 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
(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

Apr 10 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.