Post reply

Selecting particular rows

  • October 20, 2011 at 9:17 am

    #250317

    Hi everyone

     

    I have the following table in SQL Server which has this data in it:

     

    ID / LETTER SENT / CODE  

    11  /     Y /                  ZZZ

    11  /     N /                   AAA

    11  /     N /                   BBB

    11  /     N /                   CCC

    22 /      N /                   ZZZ

    22 /      N /                   TTT

    22 /      N /                   OOO

    22 /      N /                   CCC

    44  /     Y /                   ZZZ

    55 /      N /                   ZZZ

     

     

    I want to bring back all rows per ID where the Letter Sent is equal to Y (easy enough) but also cases where the letter sent is equal to N if it doesn’t also equal Y for that ID. However, I only want to bring back one of the rows maked as N. So in the above data even though ID 22 has four rows with a letter sent code of N I only want to bring back the first instance. This doesn’t matter for Y cases as there will only ever be one row per ID.

     

    Hope someone can help….

     

  • October 20, 2011 at 9:27 am

    #1397494

    I only want to bring back the first instance

    This is confusing. How can you guarantee the order of rows in a heap? Which column(s) defines the order of rows?

  • October 20, 2011 at 9:32 am

    #1397500

    Dev @ +91 973 913 6683 (10/20/2011)

    I only want to bring back the first instance

    This is confusing. How can you guarantee the order of rows in a heap? Which column(s) defines the order of rows?

    Quite right. Here's a starter for pants:

    -- CTE mimics real table

    ;with SampleTable AS (

    SELECT ID = 11, [LETTER SENT] = 'Y', CODE = 'ZZZ' UNION ALL

    SELECT 11, 'N', 'AAA' UNION ALL

    SELECT 11, 'N', 'BBB' UNION ALL

    SELECT 11, 'N', 'CCC' UNION ALL

    SELECT 22, 'N', 'ZZZ' UNION ALL

    SELECT 22, 'N', 'TTT' UNION ALL

    SELECT 22, 'N', 'OOO' UNION ALL

    SELECT 22, 'N', 'CCC' UNION ALL

    SELECT 44, 'Y', 'ZZZ' UNION ALL

    SELECT 55, 'N', 'ZZZ')

    SELECT ID, [LETTER SENT], CODE, rn1, rn2

    FROM (

    SELECT ID, [LETTER SENT], CODE,

    rn1 = ROW_NUMBER()OVER(PARTITION BY ID, [LETTER SENT] ORDER BY CODE),

    rn2 = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY [LETTER SENT] DESC)

    FROM SampleTable -- change this to your table

    ) d --WHERE rn1 = 1 AND rn2 = 1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 20, 2011 at 9:34 am

    #1397501

    Hi Dev

    Cannot guarantee order. Essentially I just want all Y rows and N rows when the customer (ID) doesn't also have a Y. The problem is they will have numerous cases of N - it doesn't actually matter what N row I return as essentially this is going to count the unique Ys and unique Ds.

    Does that make any sense?

  • October 20, 2011 at 9:39 am

    #1397505

    Chris

    That works perfectly!!

    Thanks so much...

  • October 20, 2011 at 9:41 am

    #1397507

    ByronOne (10/20/2011)

    Chris

    That works perfectly!!

    Thanks so much...

    You're welcome, thanks for the feedback.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 20, 2011 at 8:35 pm

    #1397724

    I believe we can shorten up the code just a bit... it probably won't make much of a difference in performance... (shamelessly leveraging Chris' test data)

    -- CTE mimics real table

    ;

    WITH SampleTable (ID, [LETTER SENT], CODE) AS

    (

    SELECT 11, 'Y', 'ZZZ' UNION ALL

    SELECT 11, 'N', 'AAA' UNION ALL

    SELECT 11, 'N', 'BBB' UNION ALL

    SELECT 11, 'N', 'CCC' UNION ALL

    SELECT 22, 'N', 'ZZZ' UNION ALL

    SELECT 22, 'N', 'TTT' UNION ALL

    SELECT 22, 'N', 'OOO' UNION ALL

    SELECT 22, 'N', 'CCC' UNION ALL

    SELECT 44, 'Y', 'ZZZ' UNION ALL

    SELECT 55, 'N', 'ZZZ'

    )

    SELECT d.ID, d.[LETTER SENT], d.CODE

    FROM (

    SELECT ID, [LETTER SENT], CODE,

    RowNum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [LETTER SENT] DESC, Code ASC)

    FROM SampleTable

    )d

    WHERE d.RowNum = 1

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 21, 2011 at 2:43 am

    #1397800

    Cheers Geoff - always great to see different ways of doing things...

  • October 21, 2011 at 6:50 am

    #1397937

    ByronOne (10/21/2011)

    Cheers Geoff - always great to see different ways of doing things...

    You're welcome but my name is "Jeff". It's hard enough of me to keep track of myself without having two different names. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 22, 2011 at 2:28 am

    #1398304

    Sorry Jeff - realized after I had sent the reply....

    Thanks again.

Viewing 10 posts - 1 through 9 (of 9 total)

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