I have a table structured as so:
fake_id start end misc_data
------------------------------------------------------
1 101 105 ab
1 101 105 cd
1 101 105 ef
2 117 123 gh
2 117 123 ij
2 117 123 kl
2 117 123 mn
3 51 53 op
3 51 53 qr
Notice that the fake_id field is not really a primary key, but is repeated a number of times equal to the number of distinct odd numbers in the range specified by start and end. The real id for each record is one of the odd numbers in that range. I need to write a query that returns fake_id, misc_data, and another column that contains those odd numbers to produce a real id, as follows:
fake_id real_id misc_data
------------------------------------------
1 101 ab
1 103 cd
1 105 ef
2 117 gh
2 119 ij
2 121 kl
2 123 mn
3 51 op
3 53 qr
As far as I know, there is no guarantee that there will be no gaps in the sequence (for example, there might be no records for range 21-31). How do I tell the query (or procedure, but query is preferable) that for each record with a particular fake_id, it should return the next odd number between start and end?
Also, is there a way to make the values for misc_data belong to a particular real_id? Using the second table as an example, how could I tell the query that "ab" belongs to real_id 101 instead of 103?
Thanks in advance.