How to write this select statment

Question

0.00/5 (No votes)

See more:

I have a table with some data and two keys. Key1 not null, Key2 null able
Combination of Key1 & Key2 should be unique for every row. I need to write select statement that for every given key2 returning the data from table matching Key2 or where Key2 is NULL without duplicates of key1. So, if I have two rows with the same Key1 and one of them has Key2 and another where key2 is NULL I should only return row with matching Key2. Row where key2 is NULL may or may not exists, same goes for rows where key2 is not null
Here is the example:

Data	Key1	Key2
A	1	1
B	1	NULL
C	1	2
D	2	NULL
E	3	1

Result for key2=1

Data	Key1	Key2
A	1	1
D	2	NULL
E	3	1

Result for key2 = 2

Data	Key1	Key2
C	1	2
D	2	NUL

Result for key2 =3

Data	Key1	Key2
B	1	NULL
D	2	NULL

It needs to be in single select statement to be used inside user defined function that taking kye2 as parameter

What I have tried:

I have tried several statements, but everyone fails for one of the cases
Would appreciate help on this one

Posted 1-May-19 11:46am

Greg0364

Updated 2-May-19 0:10am

CHill60

v3

Add a Solution

2 solutions

Solution 1

What if you did group by key 1 and then order by key2 desc, and select the top one from each set?

Posted 1-May-19 11:52am

Christian Graus

Comments

Greg0364 1-May-19 18:29pm

Grouping is not going to work since I need to return data as well that could be different for each row

Christian Graus 1-May-19 18:32pm

But you want one value for each key1, either the null value or a value that's not null? Or are you saying there could be lots of key2 values and you want them all, or just the null value if it's all that exists?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-05-02T00:11:00

I broke this down into the two parts
1 - Everything where column key2 matches the parameter
2 - Anything (note the change in wording) where column key2 is NULL and column key1 does not appear in the results from 1 above

I then converted that into two Common Table Expressions and UNIONed the results:

SQL

;with CTE1 AS
(
	SELECT 'CTE1' AS SOURCE, *
	FROM @tmp WHERE Key2 = @key
)
, CTE2 AS
(
	SELECT 'CTE2' AS SOURCE, *
	FROM @tmp WHERE Key2 IS NULL
	AND Key1 NOT IN (SELECT Key1 FROM CTE1)
)
SELECT * FROM CTE1 UNION SELECT * FROM CTE2
ORDER BY [Data]

I included the SOURCE column just for clarity on where the data was actually coming from - you should remove it.

In terms of setting this up as a function my @key would be the parameter to that function. Here are some instructions on how to set up a function if you are not familiar with that - SQL Server Table-Valued Function By Practical Examples[^]

Finally here is the test data I used, in case I need to come back to this question :-)

SQL

declare @tmp table ([Data] varchar(10),	Key1 int, Key2 int)
insert into @tmp ([Data], Key1, Key2) values
('A',1,	1), ('B',1,	NULL), ('C',1,	2), ('D',2,	NULL), ('E',3,	1)

declare @key int 

set @key = 3