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: 1105

  More actions

  December 17, 2018 at 5:02 am

  #376658

  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: 18257

  More actions

  December 17, 2018 at 6:35 am

  #2016488

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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 17, 2018 at 6:39 am

  #2016489

  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?

  ^{“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

• DBA.k

  Ten Centuries

  Points: 1105

  More actions

  December 17, 2018 at 6:52 am

  #2016493

  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: 1105

  More actions

  December 17, 2018 at 6:56 am

  #2016494

  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: 18257

  More actions

  December 17, 2018 at 6:58 am

  #2016495

  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: 186127

  More actions

  December 17, 2018 at 6:58 am

  #2016496

  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?

  ^{“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

• DBA.k

  Ten Centuries

  Points: 1105

  More actions

  December 17, 2018 at 7:09 am

  #2016497

  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: 1105

  More actions

  December 17, 2018 at 7:12 am

  #2016501

  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: 18257

  More actions

  December 17, 2018 at 7:20 am

  #2016503

  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