August 25, 2011 at 9:15 pm
I have a table where I would like to get only one row from a table where multiple parameters are passed and it is possible to get multiple rows. I would like to get the row with the greatest amount of specificity. I would set up the order of the results in the order I would like the tests done. Once a test passes, there are no other tests done.
Here is an example query:
CREATE TABLE MessageTest
(ID int IDENTITY(1,1),
Name varchar(50),
Number int,
Class varchar(50)
)
INSERT MessageTest (Name,Number,Class) VALUES ('Main',NULL,NULL)
INSERT MessageTest (Name,Number,Class) VALUES ('Main',NULL,'MainClass')
INSERT MessageTest (Name,Number,Class) VALUES ('Main',2,'MainClass')
INSERT MessageTest (Name,Number,Class) VALUES ('Main',1,NULL)
DECLARE @Name varchar(100),
@Number int,
@Class varchar(100)
SELECT @Name = 'Main',
@Number = 4,
@Class = 'MainClass'
SELECT ID,
Name,
Number,
Class
FROM MessageTest
WHERE ((@Name IS NOT NULL AND @Number IS NOT NULL AND @Class IS NOT NULL)
AND
(
(@Name LIKE Name + '%' AND @Number= Number AND @Class LIKE Class + '%')
OR
(@Name LIKE Name + '%' AND Number IS NULL AND @Class LIKE Class + '%')
OR
(@Name LIKE Name + '%' AND @Number = Number AND Class IS NULL)
OR
(@Name LIKE Name + '%' AND Number IS NULL AND Class IS NULL)
)
)
This query would pass back 2 rows:
1MainNULLNULL
2MainNULLMainClass
I would like to only get the row with ID = 2. I only want the row with ID = 1 if the other 3 tests don't find a record. If the second query finds something, the other 2 test shouldn't return anything. Always giving me only one message.
There will actually be multiple sets based on how many parameters are passed.
But in this case I want to pass back a record if all 3 parameters match.
If not then, if the 1st and 3rd parameter pass and there is a null value in the 2nd column.
If not then, if the 1st and 2nd parameter pass and there is a null in the 3rd column.
If none of the above pass then if the 1st one passes and the 2nd and 3rd parameters are both null.
Is there a way to structure the where clause to do this?
Thanks,
Tom
August 25, 2011 at 9:21 pm
Dynamic query, may be ???
August 26, 2011 at 12:00 am
A dynamic query wouldn't help. The issue isn't the number of parameters I send. It is the result that I get back. I have no way of knowing how many records I would get back (could be 0 - 4 records).
If I change the @Number = 2 instead of 4 and run the query I would get:
1MainNULLNULL
2MainNULLMainClass
3Main2MainClass
In this case, I would want the row where ID = 3, as that matches all the parameters.
Thanks,
Tom
August 26, 2011 at 12:26 am
I actually quite dint understand your requirement. Can you be more specific? You have been terrific in providing sample table and data, a little more explanation could get me working on this right away.
Also, there is a TOP operator, why dont you try that?
August 26, 2011 at 1:40 am
Something like this?
SELECT TOP 1 ID,
Name,
Number,
Class
...
ORDER BY
CASE WHEN Name IS NULL THEN 0 ELSE 1 END
+CASE WHEN Number IS NULL THEN 0 ELSE 1 END
+CASE WHEN Class IS NULL THEN 0 ELSE 1 END DESC
August 26, 2011 at 1:45 am
LutzM (8/26/2011)
Something like this?
SELECT TOP 1 ID,Name,
Number,
Class
...
ORDER BY
CASE WHEN Name IS NULL THEN 0 ELSE 1 END
+CASE WHEN Number IS NULL THEN 0 ELSE 1 END
+CASE WHEN Class IS NULL THEN 0 ELSE 1 END DESC
Exactly what i had in mind.. Thanks for posting it Lutz .. It seems like TOP can help him..
August 26, 2011 at 11:10 am
That was what I was looking for.
I added in the Order by without the Top just to see how it would order and it worked great.
3Main2MainClass
2MainNULLMainClass
1MainNULLNULL
Not sure what this is coming up with in the Order By but it seems to work.
What do the "+"s do in an order by?
Thanks,
Tom
August 26, 2011 at 11:26 am
tshad (8/26/2011)
That was what I was looking for.I added in the Order by without the Top just to see how it would order and it worked great.
3Main2MainClass
2MainNULLMainClass
1MainNULLNULL
Not sure what this is coming up with in the Order By but it seems to work.
What do the "+"s do in an order by?
Thanks,
Tom
The "+"s do nothing more than add the values of the three CASE statements. And each CASE statement assign 1 if the value is not null, otherwise zero. It just counts the non-null values 😉
August 26, 2011 at 4:50 pm
I see.
I can see it better if I put the case statement as a column to see what it is doing and it makes sense. Clean.
3Main2MainClass 3
2MainNULLMainClass 2
1MainNULLNULL 1
The last column being the same is a coincedence.
I understand how this works if I were to add the case statement in the select statement and then use that as an order column.
But why does this work in the Order By? Normally you have to have a valid column to do an Order By on.
Why does this work?
Thanks,
Tom
August 26, 2011 at 4:56 pm
tshad (8/26/2011)
But why does this work in the Order By? Normally you have to have a valid column to do an Order By on.Why does this work?
You can order by anything, really. Order By LEN(columnA) DESC if you want. Order By isn't dependent on a column's existence, although you can get some speed if you use a column that's also indexed in the order you want the results in.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 27, 2011 at 1:11 am
So that would be the same as:
SELECT TOP 1 ID,
Name,
Number,
Class,
CASE WHEN Name IS NULL THEN 0 ELSE 1 END
+CASE WHEN Number IS NULL THEN 0 ELSE 1 END
+CASE WHEN Class IS NULL THEN 0 ELSE 1 END DESC as SortOrder
...
ORDER BY SortOrder
Thanks,
Tom
August 27, 2011 at 1:16 am
Yes.
But you don't have to have the statement in the SELECT list. All that's required is it has to be a valid statement that can be used in the SELECT list. It doesn't have to be listed though.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply