Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

check that column has one and only one value and the value is the value I want Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 5:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 1,787, Visits: 5,722
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1586686
    Posted Thursday, June 26, 2014 6:01 PM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 4:39 PM
    Points: 1,025, Visits: 3,071
    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

    Post #1586694
    Posted Friday, June 27, 2014 7:27 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 3:32 PM
    Points: 13,455, Visits: 12,318
    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 Moden's 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)
    Post #1586823
    Posted Monday, June 30, 2014 1:25 PM


    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Tuesday, July 8, 2014 1:39 PM
    Points: 245, Visits: 735
    Thanks everyone. Sean's implementation fits the bill. I appreciate all the help.

    <><
    Livin' down on the cube farm. Left, left, then a right.
    Post #1587761
    Posted Friday, July 4, 2014 3:22 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Thursday, September 11, 2014 1:58 AM
    Points: 2,397, Visits: 3,407
    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"
    Post #1589248
    Posted Friday, July 4, 2014 10:40 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, July 15, 2014 3:09 AM
    Points: 314, Visits: 2,530
    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

    Post #1589432
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse