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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10207 Visits: 7891
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
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2762 Visits: 3318
    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
    SSC Guru
    SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

    Group: General Forum Members
    Points: 60575 Visits: 17954
    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
    Ten Centuries
    Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

    Group: General Forum Members
    Points: 1027 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
    SSCrazy Eights
    SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

    Group: General Forum Members
    Points: 9293 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
    SSCommitted
    SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

    Group: General Forum Members
    Points: 1519 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