getting nearest result within IFNULL

Question

I've got a maintenance script which dumps a bunch of data from one db to another.

I'm trying to get the data as

  SELECT id, IFNULL(rank1,(SELECT rank2 
  FROM table  
  WHERE rank1 IS NOT NULL and 
        rank2<rank2 of current row 
  ORDER BY rank2 LIMIT 1)) FROM table

What I'm attempting to get with the rank2 of current row, is the nearest rank2 where rank1 is not null. I'm making the assumption that rank1 is an effective replacement for rank2

So, i believe I have two problems.

1. I don't think I have rank2 to be used in the nested select statement
2. I don't know how to say 'get closest rank2<rank2 to current.

The values I have for Rank1 range from 0-20,000, and rank2 ranges from 0-150,000 (not sure why that would matter). There isn't an effective correlation between the ranks.

Rank1 is always a more reliable figure, but is often null, so I'm trying to fudge the my order with this type of a replacement.

Here's a bit of sample data to use as an example

id            rank1            rank2
1             120,000          14,000
2             120,000          18,420
3             126,000          15,500
4             85,000            NULL
5             75,000           16,000
6             70,000           15,700
7             68,000            NULL
8             42,000            NULL
9             26,000            NULL
10            21,500           8,000

I would hope to get back an order of 2,5,4,6,7,3,1,8,9,10. or something close to that. Basically, when I have a null for rank2 get the nearest rank2 for the nearest rank1.

I don't expect this to be 'perfect', but better than just sorting on rank1.

Answer 1

I'm not 100% sure what you're asking. Would COALESCE(rank1, rank2, rankX) where it would return the first non-null value work for you?