October 18, 2013 at 12:19 pm
Hello everybody.
I am a researcher looking for answers.
I have a portfolio of select queries, and am looking for a simple (?) modification.
use table
Go
Select *
from table_name
where
condition 1
condition 2
"
"
""""""""
condition 10
This only returns rows where all 10 conditions are true. Is there a simple way to return all rows where 8 or more of the where statements are true?
October 18, 2013 at 12:26 pm
Pretty sparse on details here but I think you could do something like this. I'm not so sure about the "simple" part but it should work.
select * from
(
Select *
, case when condition1 then 1 else 0 end
+ case when condition2 then 1 else 0 end
+ case when condition3 then 1 else 0 end as ConditionCount
from table_name
where
condition 1
OR condition 2
) x
where ConditionCount >= 8
This is is a rather strange scenario. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 12:39 pm
I tested a similar solution successfully...
SELECT *
FROM table
WHERE (
CASE
WHEN condition1 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition2 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition3 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition4 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition5 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition6 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition7 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition8 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition9 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition10 = 'xxx'
THEN 1
ELSE 0
END
) >= 8
October 18, 2013 at 12:51 pm
Thanks Sean. I'll test it later when I get home. I was trying to use a variable, but I can see how this would work.
October 18, 2013 at 12:53 pm
Thanks Batgirl.
(I've always wanted to say that!)
I'll check this later.
October 18, 2013 at 12:53 pm
Nice! It seems like a strange request but I get it often. The boss generally wants a few exact match clauses and a subset of several "fuzzy match" clauses to determine what gets combined when I import awful duplicate customer data.
Last week I had a tougher scenario of joining about an average of a dozen rows into one (from thousands) where they matched each other on various subsets of many clauses.
October 18, 2013 at 1:10 pm
kennethrbell (10/18/2013)
Thanks Sean. I'll test it later when I get home. I was trying to use a variable, but I can see how this would work.
You're welcome. Please post back with an update when you get a chance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 2:14 pm
Bill Talada (10/18/2013)
Nice! It seems like a strange request but I get it often. The boss generally wants a few exact match clauses and a subset of several "fuzzy match" clauses to determine what gets combined when I import awful duplicate customer data.Last week I had a tougher scenario of joining about an average of a dozen rows into one (from thousands) where they matched each other on various subsets of many clauses.
We do a similar query where there are multiple columns to test a match against and those matches are weighted
case when col1 = val1 then 30 when col1 = other1 then 20 else 0 end +
case when col2 = val2 then 5 else 0 end +
...
However I've found that using this in a where clause can really make the query plan stink:w00t:
October 18, 2013 at 6:55 pm
Thanks everybody. I'd been trying to figure this out for a while. I was using local variables with a case function which apparently don't work in a select statement. I even posed the question to a local SQL users group that meets on Microsoft campus, they thought it was an odd question too. Aah, the life of datamining.
I was able to get Batgirl's query to work. I love the linear regression idea, too. Maybe that will be the next version. I need to figure what values to assign!
Sean, I tried yours but got lost somewhere. Probably because I have several conditions (i.e. where clauses) that I don't want in the count but are universal. I figure if I filter by those first it will speed up the query.
So here is my slight modification of Batgirls query.
SELECT *
FROM table
WHERE
--these UniversalConditions (where clauses) are mandatory all must be true
UniversalCondition1
UniversalCondition2
UniversalCondition3
UniversalCondition4
--inside the Case Statement are Variable Conditions where I will accept 8
--out of 10
(
CASE
WHEN condition1 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition2 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition3 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition4 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition5 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition6 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition7 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition8 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition9 = 'xxx'
THEN 1
ELSE 0
END + CASE
WHEN condition10 = 'xxx'
THEN 1
ELSE 0
END
) >= 8
Is there always one more question?
Mine is, "is there a way to have the total count display as well?" In other words, is this row a "10" a "9" or an "8"?
October 21, 2013 at 7:18 am
kennethrbell (10/18/2013)
Is there always one more question?Mine is, "is there a way to have the total count display as well?" In other words, is this row a "10" a "9" or an "8"?
Adding the count to the output is what the code I posted does. 😛
It really isn't too complicated. I just put the case expression as a column instead of in the where clause then turned the entire query into a subquery. That allows to then filter the query and/or display the count.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2013 at 11:31 am
Hey Sean,
Boy how time flies.
I just wanted to thank you for your code.
This particular project got reprioritized by my boss (I'm sure you know how that goes.) Yet, I'm certain it will re-surface.
I appreciate your responses.
October 25, 2013 at 11:51 am
kennethrbell (10/25/2013)
Hey Sean,Boy how time flies.
I just wanted to thank you for your code.
This particular project got reprioritized by my boss (I'm sure you know how that goes.) Yet, I'm certain it will re-surface.
I appreciate your responses.
You are quite welcome. The upside is you learned a couple of new tricks along the way. The stuff you picked up will stay with you long after the project gets trashed. 😀 Come back anytime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply