Search code examples
mysql

Get last non null field in SQL row

I have been racking my brain trying to figure this out. I am not an expert in SQL by any means, but here's what I'm trying to do. I have a table in SQL where the Row is specific to a vehicle and the columns define ownership change dates. As you can see, some vehicles have only two ownership dates listed and some have six or more. How can I get SQL to tell me that on a given row (or record) that the last Non Null Ownership date is XXX?

Sample of table

Everything I've found thus far uses last null in the entire table, so the flip of what I'm trying to accomplish. It's looking to give me the last non null item of a column and not row. I may have upwards of 1M+ rows so I can't really flip the design of this chart.

Record Number Oldest Ownership Date of Record
Line 1 20050930
Line 2 20060508
Line 3 20061025

... and so on...

Solution

  • Just use COALESCE() with the columns in reverse order:

    SELECT COALESCE(DateOwnership11, DateOwnership10, DateOwnership9, ..., DateOwnership1)