Hi Team , Please help.. I have data in one table based on same id we have data some specific column i need to get first NOT NULL value . Incase in 1st row NULL second row 200 3rd roe 300 we have i need to select second row 200 only.

  • DBA.k

    Ten Centuries

    Points: 1085

    LEGALPARCELID SEQUENCE_NO LEGALDESCRIPTION PLANNO
    0102824230001 002  
    LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6
    NULL
    0102824230001 993 NULL 15510
    0102824230001 994 NULL 15610
    0102824230001 995 NULL NULL

    Above is the current data in my table but i need below output. First not null value i need each column based on SEQUENCE_NO. I have 5 more columns also in this table. . not MAX/MIN value .. In case if any column i have all null values i need to place null.. Please help on this

    My Output Should be like below
    LEGALPARCELID LEGALDESCRIPTION PLANNO
    0102824230001  LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6 15510
  • Jonathan AC Roberts

    SSCoach

    Points: 17288

    SELECT TOP(1) *
    FROM myTable
    WHERE LEGALDESCRIPTION IS NOT NULL
    ORDER BY LEGALPARCELID, SEQUENCE_NO

  • ChrisM@Work

    SSC Guru

    Points: 186107

    DBA.A - Monday, December 17, 2018 5:02 AM

    LEGALPARCELID SEQUENCE_NO LEGALDESCRIPTION PLANNO
    0102824230001 002  
    LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6
    NULL
    0102824230001 993 NULL 15510
    0102824230001 994 NULL 15610
    0102824230001 995 NULL NULL

    Above is the current data in my table but i need below . First not null value i need . not MAX/MIN value .. Please help on this

    My Output Should be like below
    LEGALPARCELID LEGALDESCRIPTION PLANNO
    0102824230001  LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6 15510

    Which columns can be null?
    "First" of what sequence?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • DBA.k

    Ten Centuries

    Points: 1085

    Jonathan AC Roberts - Monday, December 17, 2018 6:35 AM

    SELECT TOP(1) *
    FROM myTable
    WHERE LEGALDESCRIPTION IS NOT NULL
    ORDER BY LEGALPARCELID, SEQUENCE_NO

    Thanks for the response.. But this will not work.. Hear multiple rows i have with single id .. but each column i need to select first not null value , if any column have all null value i need to place null..

  • DBA.k

    Ten Centuries

    Points: 1085

    ChrisM@Work - Monday, December 17, 2018 6:39 AM

    DBA.A - Monday, December 17, 2018 5:02 AM

    LEGALPARCELID SEQUENCE_NO LEGALDESCRIPTION PLANNO
    0102824230001 002  
    LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6
    NULL
    0102824230001 993 NULL 15510
    0102824230001 994 NULL 15610
    0102824230001 995 NULL NULL

    Above is the current data in my table but i need below . First not null value i need . not MAX/MIN value .. Please help on this

    My Output Should be like below
    LEGALPARCELID LEGALDESCRIPTION PLANNO
    0102824230001  LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6 15510

    Which columns can be null?
    "First" of what sequence?

    Thanks for the reply .. Based on Sequence_ID and LEGALPARCELID  i need to select each column first NOT NULL value. In case if any column have all rows with group by SEQID and LEGALPARCELID with NULL then i need to place NULL only.

  • Jonathan AC Roberts

    SSCoach

    Points: 17288

    DBA.A - Monday, December 17, 2018 6:52 AM

    Jonathan AC Roberts - Monday, December 17, 2018 6:35 AM

    SELECT TOP(1) *
    FROM myTable
    WHERE LEGALDESCRIPTION IS NOT NULL
    ORDER BY LEGALPARCELID, SEQUENCE_NO

    Thanks for the response.. But this will not work.. Hear multiple rows i have with single id .. but each column i need to select first not null value , if any column have all null value i need to place null..

    ;WITH CTE AS
    (
        SELECT DISTINCT LEGALPARCELID
          FROM myTable t
    )
    SELECT CTE.LEGALPARCELID, t2.*
      FROM CTE
     OUTER APPLY(SELECT TOP(1) *
                   FROM myTable t2
                  WHERE t2.LEGALPARCELID = t.LEGALPARCELID
                    AND t2.LEGALDESCRIPTION IS NOT NULL
                   ORDER BY SEQUENCE_NO) t2

  • ChrisM@Work

    SSC Guru

    Points: 186107

    DBA.A - Monday, December 17, 2018 6:56 AM

    ChrisM@Work - Monday, December 17, 2018 6:39 AM

    DBA.A - Monday, December 17, 2018 5:02 AM

    LEGALPARCELID SEQUENCE_NO LEGALDESCRIPTION PLANNO
    0102824230001 002  
    LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6
    NULL
    0102824230001 993 NULL 15510
    0102824230001 994 NULL 15610
    0102824230001 995 NULL NULL

    Above is the current data in my table but i need below . First not null value i need . not MAX/MIN value .. Please help on this

    My Output Should be like below
    LEGALPARCELID LEGALDESCRIPTION PLANNO
    0102824230001  LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6 15510

    Which columns can be null?
    "First" of what sequence?

    Thanks for the reply .. Based on Sequence_ID and LEGALPARCELID  i need to select each column first NOT NULL value. In case if any column have all rows with group by SEQID and LEGALPARCELID with NULL then i need to place NULL only.

    Are you expecting one row for each individual value of LEGALPARCELID, or one row for the entire dataset?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • DBA.k

    Ten Centuries

    Points: 1085

    ChrisM@Work - Monday, December 17, 2018 6:58 AM

    DBA.A - Monday, December 17, 2018 6:56 AM

    ChrisM@Work - Monday, December 17, 2018 6:39 AM

    DBA.A - Monday, December 17, 2018 5:02 AM

    LEGALPARCELID SEQUENCE_NO LEGALDESCRIPTION PLANNO
    0102824230001 002  
    LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6
    NULL
    0102824230001 993 NULL 15510
    0102824230001 994 NULL 15610
    0102824230001 995 NULL NULL

    Above is the current data in my table but i need below . First not null value i need . not MAX/MIN value .. Please help on this

    My Output Should be like below
    LEGALPARCELID LEGALDESCRIPTION PLANNO
    0102824230001  LOT 6 AND THE S 8 FT OF LOT 5, LOTS 5 AND 6 15510

    Which columns can be null?
    "First" of what sequence?

    Thanks for the reply .. Based on Sequence_ID and LEGALPARCELID  i need to select each column first NOT NULL value. In case if any column have all rows with group by SEQID and LEGALPARCELID with NULL then i need to place NULL only.

    Are you expecting one row for each individual value of LEGALPARCELID, or one row for the entire dataset?

    Yes Each Individual LegalparcelID i need each column first not null value in row, I have Description , parcelno,status etc column. groupby LegalpID and select each column first not null value.

  • DBA.k

    Ten Centuries

    Points: 1085

    Jonathan AC Roberts - Monday, December 17, 2018 6:58 AM

    DBA.A - Monday, December 17, 2018 6:52 AM

    Jonathan AC Roberts - Monday, December 17, 2018 6:35 AM

    SELECT TOP(1) *
    FROM myTable
    WHERE LEGALDESCRIPTION IS NOT NULL
    ORDER BY LEGALPARCELID, SEQUENCE_NO

    Thanks for the response.. But this will not work.. Hear multiple rows i have with single id .. but each column i need to select first not null value , if any column have all null value i need to place null..

    ;WITH CTE AS
    (
        SELECT DISTINCT LEGALPARCELID
          FROM myTable t
    )
    SELECT CTE.LEGALPARCELID, t2.*
      FROM CTE
     OUTER APPLY(SELECT TOP(1) *
                   FROM myTable t2
                  WHERE t2.LEGALPARCELID = t.LEGALPARCELID
                    AND t2.LEGALDESCRIPTION IS NOT NULL
                   ORDER BY SEQUENCE_NO) t2

    I am sorry.. I should not filter LEGALDESCRIPTION is not null because for another LEGALPARCELID we have change LEGALDESCRIPTION IS NULL FOR all rows but PARCELNO we have some data that i need to select..

  • Jonathan AC Roberts

    SSCoach

    Points: 17288

    DBA.A - Monday, December 17, 2018 7:12 AM

    Jonathan AC Roberts - Monday, December 17, 2018 6:58 AM

    DBA.A - Monday, December 17, 2018 6:52 AM

    Jonathan AC Roberts - Monday, December 17, 2018 6:35 AM

    SELECT TOP(1) *
    FROM myTable
    WHERE LEGALDESCRIPTION IS NOT NULL
    ORDER BY LEGALPARCELID, SEQUENCE_NO

    Thanks for the response.. But this will not work.. Hear multiple rows i have with single id .. but each column i need to select first not null value , if any column have all null value i need to place null..

    ;WITH CTE AS
    (
        SELECT DISTINCT LEGALPARCELID
          FROM myTable t
    )
    SELECT CTE.LEGALPARCELID, t2.*
      FROM CTE
     OUTER APPLY(SELECT TOP(1) *
                   FROM myTable t2
                  WHERE t2.LEGALPARCELID = t.LEGALPARCELID
                    AND t2.LEGALDESCRIPTION IS NOT NULL
                   ORDER BY SEQUENCE_NO) t2

    I am sorry.. I should not filter LEGALDESCRIPTION is not null because for another LEGALPARCELID we have change LEGALDESCRIPTION IS NULL FOR all rows but PARCELNO we have some data that i need to select..

    I don't understand exactly what you need but I think you want the first NOT NULL value for each column by some order (you haven't specified the order). You can just extend the above SQL for all columns:
    ;WITH CTE AS
    (
      SELECT DISTINCT LEGALPARCELID
       FROM myTable t
    )
    SELECT CTE.LEGALPARCELID,
           t2.LEGALDESCRIPTION,
           t3.PLANNO
      FROM CTE
     OUTER APPLY(SELECT TOP(1) LEGALDESCRIPTION 
                   FROM myTable t2
                  WHERE t2.LEGALPARCELID = t.LEGALPARCELID
                    AND t2.LEGALDESCRIPTION IS NOT NULL
                  ORDER BY SEQUENCE_NO) t2
     OUTER APPLY(SELECT TOP(1) PLANNO 
                   FROM myTable t2
                  WHERE t2.LEGALPARCELID = t.LEGALPARCELID
                    AND t2.PLANNO IS NOT NULL
                  ORDER BY SEQUENCE_NO) t3

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

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