How to fill in null values in 1st row using values in 2nd row?-CodePudding

I am trying to write a query that shows only the 1st rows for each name, but those rows have a null for the title, so I want to pull in their titles from the immediate next row.

table1

Name	Title	Row
Dan	NULL	1
Dan	Engineer	2
Dan	Developer	3
Jay	NULL	1
Jay	Lawyer	2

The final result should look like the following:

Name	Title	Row
Dan	Engineer	1
Jay	Lawyer	1

I've only written this so far, I don't know how to pull in the titles from the previous row. Any help would be greatly appreciated.

select *
from table1
where Row = 1

CodePudding user response：

I'd say the simplest way to achieve this is by grouping and excluding any rows having null value.

select * from table1 where Title IS NOT NULL GROUP BY Name

Should work like a charm

CodePudding user response：

Use below approach

select name, lead(title) over win as title, row
from your_table
qualify 1 = row_number() over win
window win as (partition by name order by row)

if applied to sample data in your question output is

CodePudding user response：

This should work:

SELECT 
  T1.Name, 
  (CASE WHEN T1.Title IS NULL 
        THEN (SELECT TOP(1) T2.Title FROM table1 T2 WHERE T2.Name = T1.Name AND T2.Title IS NOT NULL ORDER BY T2.Row ASC) ELSE T1.Title END) AS 'Title', 
  T1.Row
FROM table1 T1
WHERE T1.Row = 1

The query checks if the Title is NULL and in that case selects first title for that name that's not NULL otherwise returns Title of 1st row.

CodePudding user response：

Here is a simple solution using the lead( ) window function.

SELECT Name, IFNULL(Title, LeadTitle), Row
FROM (
  SELECT Name, Title, LEAD(Title) OVER(PARTITION BY Name ORDER BY ROW) AS LeadTitle, Row
  FROM `dataset.tablename` )
WHERE Row = 1