mysql - How to sort a list of people by birth and death dates when data are incomplete -

i have list of people may or may not have birth date and/or death date. want able sort them meaningfully - subjective term - birth date.

but - if don't have birth date have death date, want have them collated list proximal other people died then.

i recognize not discrete operation - there ambiguity should go when birth date missing. i'm looking approximation, of time.

here's example list of i'd like:

alice     1800     1830 bob       1805     1845 carol              1847 don       1820     1846 esther    1825     1860 

in example, i'd happy carol appearing either before or after don - that's ambiguity i'm prepared accept. important outcome carol sorted in list relative death date death date, not sorting death dates in birth dates.

what doesn't work if coalesce or otherwise map birth , death dates together. example, order birth_date, death_date put carol after esther, way out of place thinking.

i think you're going have calculate average age people end living (for having both birth , death dates). , either subtract them death date or add them birth date people don't have other one.

doing in 1 query may not efficient, , perhaps ugly because mysql doesn't have windowing functions. may better of precalculating average living age beforehand. let's try in 1 query anyway:

select   name, birth_date, death_date     people order coalesce(     birth_date,     date_sub(death_date, interval (         select avg(datediff(death_date, birth_date))         people          birth_date not null , death_date not null     ) day) )