SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4110 Visits: 7865
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • mickyT
    mickyT
    SSCommitted
    SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

    Group: General Forum Members
    Points: 1570 Visits: 3317
    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
    Sean Lange
    Sean Lange
    One Orange Chip
    One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

    Group: General Forum Members
    Points: 25952 Visits: 17519
    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.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
    Understanding and Using APPLY (Part 1)
    Understanding and Using APPLY (Part 2)
    Tobar
    Tobar
    SSC-Addicted
    SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

    Group: General Forum Members
    Points: 455 Visits: 758
    Thanks everyone. Sean's implementation fits the bill. I appreciate all the help.

    <><
    Livin' down on the cube farm. Left, left, then a right.
    SwePeso
    SwePeso
    Hall of Fame
    Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

    Group: General Forum Members
    Points: 3991 Visits: 3433
    SELECT      Marker
    FROM dbo.TEMP_A
    GROUP BY Marker
    HAVING SUM(CASE WHEN Val = 'Y' THEN 0 ELSE 1 END) = 0




    N 56°04'39.16"
    E 12°55'05.25"
    Sachin Nandanwar
    Sachin Nandanwar
    Say Hey Kid
    Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

    Group: General Forum Members
    Points: 683 Visits: 2633
    Using SQL Server 2012 function in use

    select * from
    (
    select
    *,CUME_DIST () OVER (PARTITION BY marker ORDER BY val)-
    RANK() OVER (PARTITION BY marker ORDER BY val ) AS PctRank
    from
    #Temp t
    )T1 where PctRank=0



    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search