check that column has one and only one value and the value is the value I want

  • Is there an elegant way to solve the question "I only want to show widgets that have a value "Y" and only if all the entries for widget have a value "Y".

    here is some test data

    select 'A' marker, 'Y' val

    into TEMP_A

    union all

    select 'B', 'N'

    union all

    select 'B', 'Y'

    select * from TEMP_A

    markerval

    AY

    BN

    BY

    What I want is a single query that will only return 'A' (it is the only marker that has a val of 'Y' and only 'Y')

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • May well be a more efficient way to do this, but here's a shot!

    select marker from TEMP_A

    WHERE val = 'Y'

    EXCEPT

    SELECT marker FROM TEMP_A

    WHERE val = 'N'

    - 😀

  • Here is another way to do this. This method prevents accessing the table a second time. Not sure it is more efficient in your real world situation. It does however make this a bit easier if you have more values than 'Y' and 'N' in your actual table.

    select marker

    , MIN(val) as val

    from TEMP_A

    group by marker

    having MIN(val) = MAX(val)

    and MIN(val) = 'Y'

    _______________________________________________________________

    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/

  • Here's another alternative that should perform well as it not only uses just one pass on the table, but uses NULLIF which usually is rather fast:

    SELECT marker

    FROM TEMP_A

    GROUP BY marker

    HAVING COUNT(NULLIF(val, 'N')) = COUNT(*);

    As COUNT will only look at non-NULL values, NULLIF is an easy way to turn non-desired values into NULL, and thus return only the groups where the counts are equal, which can only happen when there are no 'N' values in the group.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Aha! I was fumbling with trying to come up with some sort of aggregate to avoid a double-pass on the table, but I couldn't figure it out. I'll just file those handy tricks away in the experimentation folder 😀

    - 😀

  • Here is another option, requires two passes of the table though.

    create table #Temp(Marker char(1), val char(1));

    insert into #Temp

    values ('A','Y'),('B','N'),('B','Y');

    select

    *

    from

    #Temp t

    where

    not exists(select

    1

    from

    #Temp t1

    where

    t.Marker = t1.Marker and

    t1.val <> 'Y');

    drop table #Temp;

  • And here, using the minimal sample set, are the execution plans for the suggested solutions.

    create table #Temp(Marker char(1), val char(1));

    insert into #Temp

    values ('A','Y'),('B','N'),('B','Y');

    select

    *

    from

    #Temp t

    where

    not exists(select

    1

    from

    #Temp t1

    where

    t.Marker = t1.Marker and

    t1.val <> 'Y');

    select

    Marker,

    MIN(val) as val

    from

    #Temp

    group by

    Marker

    having

    MIN(val) = MAX(val)

    and MIN(val) = 'Y';

    SELECT

    Marker

    FROM

    #Temp

    GROUP BY

    Marker

    HAVING

    COUNT(NULLIF(val, 'N')) = COUNT(*);

    select

    Marker

    from

    #Temp

    WHERE

    val = 'Y'

    EXCEPT

    select

    Marker

    from

    #Temp

    WHERE

    val = 'N';

    drop table #Temp;

  • With the same requirements, if you add a Marker with a third value for val, one of the options provided fails. Run this:

    create table #Temp(Marker char(1), val char(1));

    insert into #Temp

    values ('A','Y'),('B','N'),('B','Y'),('C','Z');

    select

    *

    from

    #Temp t

    where

    not exists(select

    1

    from

    #Temp t1

    where

    t.Marker = t1.Marker and

    t1.val <> 'Y');

    select

    Marker,

    MIN(val) as val

    from

    #Temp

    group by

    Marker

    having

    MIN(val) = MAX(val)

    and MIN(val) = 'Y';

    SELECT

    Marker

    FROM

    #Temp

    GROUP BY

    Marker

    HAVING

    COUNT(NULLIF(val, 'N')) = COUNT(*);

    select

    Marker

    from

    #Temp

    WHERE

    val = 'Y'

    EXCEPT

    select

    Marker

    from

    #Temp

    WHERE

    val = 'N';

    drop table #Temp;

  • EDIT: Yep, Lynn, that would be mine. Oops... that's not right. It's the queries using MAX and MIN. The 'Z' value would throw those queries off. END EDIT

    My query IS dependent on the initial conditions, but with so little detail on what the real world scenario might be, I try not to make up new conditions, and try to rely on some level of common sense on the part of the original poster. If it then fails because the actual conditions are other than what was posted, then the OP learns something about how to describe a problem in sufficient detail, whereas if I try to cover all possible angles, the OP often learns relatively little, as this often ends up with post after post after post of questions and non-answers until one side or the other either finally figures it out or gives up.

    EDIT: Finally, with 'Y' and 'N' as the values for "val", I think it's a reasonably safe assumption that there are no other values for that field, making it safe to use MAX and MIN or other binary choice methods. At least as safe as one can be given the nature of the forum, anyway... :-):-)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (6/26/2014)


    Yep, Lynn, that would be mine. It IS dependent on the initial conditions, but with so little detail on what the real world scenario might be, I try not to make up new conditions, and try to rely on some level of common sense on the part of the original poster. If it then fails because the actual conditions are other than what was posted, then the OP learns something about how to describe a problem in sufficient detail, whereas if I try to cover all possible angles, the OP often learns relatively little, as this often ends up with post after post after post of questions and non-answers until one side or the other either finally figures it out or gives up.

    Finally, with 'Y' and 'N' as the values for "val", I think it's a reasonably safe assumption that there are no other values for that field. At least as safe as one can be given the nature of the forum, anyway... :-):-)

    You are correct, many times we get only a partial explanation of the problem and our solutions don't always work when they get put into a more robust test environment (hopefully) or into production (I really hope not).

    I was just doing a what if there were other values and the OP still wanted only those rows with a specific value.

  • Sean Lange (6/26/2014)


    Here is another way to do this. This method prevents accessing the table a second time. Not sure it is more efficient in your real world situation. It does however make this a bit easier if you have more values than 'Y' and 'N' in your actual table.

    select marker

    , MIN(val) as val

    from TEMP_A

    group by marker

    having MIN(val) = MAX(val)

    and MIN(val) = 'Y'

    I like this most, and it definitely does not suffer from "Z" or any other value being in the data. ? ?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I'd vote for Lynn's personally. His will return all the rows for Markers that have multiple rows all have Val 'Y'. This would become important if there are other columns to be returned.

    For example

    'A','Y',0.01

    'A','Y',1.24

    'B','Y',1.4

    'B','N',5.47

  • mickyT (6/26/2014)


    I'd vote for Lynn's personally. His will return all the rows for Markers that have multiple rows all have Val 'Y'. This would become important if there are other columns to be returned.

    For example

    'A','Y',0.01

    'A','Y',1.24

    'B','Y',1.4

    'B','N',5.47

    This would be a change in the requirements. For this type of thing I would agree but that is not at all what was originally posted.

    The upside is that we have come up with a number of ways to accomplish this. They all have merit and will work in certain situations. There is no "best" way for much of anything in sql server across the board. Everything must be evaluated with reference to the task at hand.

    _______________________________________________________________

    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/

  • Thanks everyone. Sean's implementation fits the bill. I appreciate all the help.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • SELECTMarker

    FROMdbo.TEMP_A

    GROUP BYMarker

    HAVINGSUM(CASE WHEN Val = 'Y' THEN 0 ELSE 1 END) = 0


    N 56°04'39.16"
    E 12°55'05.25"

  • Viewing 15 posts - 1 through 15 (of 15 total)

    You must be logged in to reply to this topic. Login to reply