CODEWITHSUNDEEP
sqloracle-databaseplsql
Pedro
Pedro

Reputation: 385

How to generate a column with a series of numbers based on a min and max value

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.

Upvotes: 0

Views: 519

Answers (2)

Pedro
Pedro

Reputation: 385

Apologies for not answering sooner or to the individual comments. @John Dewey, I believe when I tried your script it did not correctly keep the gaps between the start-end series, but I was motivated to learn more about the PARTITION keyword and I think I am more enlightened now.

Since this was for an ETL task, I ended up writing code to generate the real IDs in a loop on the extract (I guess it would also count as a transform) side.

Upvotes: 0

John Dewey
John Dewey

Reputation: 7093

Guessing here that you plan to sort on misc_data:

SELECT "fake_id",
       ((ROW_NUMBER()OVER(PARTITION BY "start"
                        ORDER BY "misc_data")-1)*2)+"start" AS "real_id",
       "misc_data"
FROM t
ORDER BY "misc_data";

http://www.sqlfiddle.com/#!4/ae23c/23

Upvotes: 4

Related Questions