Variable select query

  • 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?

  • 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/

  • 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

  • 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.

  • Thanks Batgirl.

    (I've always wanted to say that!)

    I'll check this later.

  • 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.

  • 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/

  • 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:

  • 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"?

  • 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/

  • 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.

  • 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