Select last nontrivial value from each column, by group

How might I write in SQL a query that accumulates a table of rows representing selective updates into a single “latest and greatest” row per BY group?

For instance from the following table:

index	date	ssn	first	last	title	car	shoe
1	Apr 1	100-00-0001	Joseph	Schmoe	Analyst	Honda	Adidas
2	May 1	100-00-0001	Joe
3	May 16	100-00-0001					Nike
4	June 20	100-00-0001			Sr Analyst
5	Jul 13	200-22-2222	Jane	Doe	Architect
6	Aug 4	100-00-0001				Tesla
7	Sep 9	100-00-0001			VP		All birds
8	Sep 9	200-22-2222					Hoka
9	Oct 4	100-00-0001			”		Timberlands
10	Nov 9	100-00-0001				Jeep
11	Dec 4	200-22-2222			Principal

I’d like a query that returns something like this:

index	date	ssn	first	last	title	car	shoe
10	Nov 9	100-00-0001	Joe	Schmoe	”	Jeep	Timberlands
11	Dec 4	200-22-2222	Jane	Doe	Principal		Hoka

My first instinct is there might be some aggregate function analogous to MAX() that works on an ordered set of rows, e.g. LAST(), e.g. something like:

  SELECT 
      LATEST(index) OVER (PARTITION BY ssn ORDER BY date) AS index,
      LATEST(date) OVER (PARTITION BY ssn ORDER BY date) AS date,
      LATEST(ssn) OVER (PARTITION BY ssn ORDER BY date) AS ssn,
      LATEST(first) OVER (PARTITION BY ssn ORDER BY date) AS first,
      LATEST(last) OVER (PARTITION BY ssn ORDER BY date) AS last,
      LATEST(title) OVER (PARTITION BY ssn ORDER BY date) AS title,
      LATEST(car) OVER (PARTITION BY ssn ORDER BY date) AS car,
      LATEST(shoe) OVER (PARTITION BY ssn ORDER BY date) AS shoe
  FROM myschema.updates
  GROUP BY ssn

Apologies if this is trivial, I just don’t yet know what term to search for.

Answer

If you want the most recent not-null, not-blank value of each column, for each ssn, reported against the last date, then you can use something like this:

 select 
     s1.ssn
  , max(s1.date) as date
  , max(case when s1.first_rn=1 then first else null end) as first
  , max(case when s1.last_rn=1 then last else null end) as last
from (  
   select t.ssn,
         t.date,
         t.first,
         t.last,
         row_number() over (partition by ssn 
                            order by  case when coalesce(first,'')='' 
                                           then 2 else 1 
                                      end asc
                                     ,  date desc
                           ) as first_rn,
         row_number() over (partition by ssn 
                            order by  case when coalesce(last,'')='' 
                                           then 2 else 1 
                                       end asc
                                     , date desc
                           ) as last_rn

    from Tbl t
) s1
group by s1.ssn

We first find the most recent row (marking it with xxx_rn=1) with non-empty column (by making sure that nulls/blanks come last in numbering), then in the outer level we summarise, picking up values for each ssn by inspecting the rows with xxx_rn=1 (you could use max or min, it wouldn’t matter, because there will only be one xxx_rn=1 record for each ssn in the subquery; min/max will ignore all the others). I have only demonstrated on first and last, you need to repeat for the other columns.