How do select a more specific value in TSQL

Question

0.00/5 (No votes)

See more:

So I have a table storing a bunch of default values, where those values could be potentially overridden. I need a query to select the default value if no specific has been found.

In the example below, F1 is a default value if F2 is null. If F2 is not null, I want to return that instead.

So I can solve this by first seeing if specific rows exist, then selecting those rows otherwise selecting the default rows. It just feels really clunky and repetitive. Is way to include this all in a single query?

What I have tried:

SQL

DECLARE @x char = 'A'

DECLARE @t TABLE (
	F1 char,
	F2 int null
)

INSERT INTO @t (F1, F2) VALUES ('A', 1)
INSERT INTO @t (F1, F2) VALUES ('A', NULL)
INSERT INTO @t (F1, F2) VALUES ('B', 2)
INSERT INTO @t (F1, F2) VALUES ('C', NULL)

IF EXISTS(
			SELECT * 
			FROM @t
			WHERE F1 = @x AND
				F2 IS NOT NULL  
		)
BEGIN
	SELECT * 
	FROM @t
	WHERE F1 = @x AND
		F2 IS NOT NULL  
END 
ELSE
BEGIN
	SELECT * 
	FROM @t
	WHERE F1 = @x AND
		F2 IS NULL  
END

/*
This correctly returns 

when @x = 'A'
F1 F2
-------
A 1

when @x = 'B'
F1 F2
-------
B 2

when @x = 'C'
F1 F2
-------
C NULL

*/

Posted 16-Aug-18 22:50pm

TheRedEye

Updated 19-Aug-18 21:17pm

Add a Solution

Comments

[no name] 17-Aug-18 13:51pm

F1 is a default value if F2 is null

Makes no sense.

1 solution

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)

Member 7870345 · Answer 1 · 2018-08-19T21:18:00

Solution 1

If your table is named 't_example' you could use this select stament:

SQL

SELECT  f1,MAX(F2) AS F2
	FROM t_example
	GROUP  BY F1
	ORDER BY 1

This produces the following result:
F1 F2
A 1
B 2
C NULL

If you want you can add a 'where' like:

SQL

SELECT  f1,MAX(F2) AS F2
	FROM t_example
	WHERE F1='B'
	GROUP  BY F1

Posted 19-Aug-18 21:18pm

Member 7870345

Comments

CHill60 20-Aug-18 4:21am

Good solution. The only thing I would add would be to explain why this works ... "Except for COUNT, aggregate functions ignore null values." See Aggregate Functions (Transact-SQL) | Microsoft Docs[^]