0

I need to generate a new column in a table in a sql database.

the given table is: 

id1     value1    value2    value3    value4
9465    387        801      1990        20

All columns are integer. value1 and value2 are always 3 digits, value3 are year value, value4 is not more than 3 digits.

I need to generate a value by combining value1 to value4, suppose that it is called "value_combine". The "value_combine" should be unique. For example, given the different combinations of value1 to value4, the "value_combine" should also be different.

And then, the "value_combine" is combined with id1 such that the new value (we call it final_id) should be unique.

For example, given different combinations of id1 and value_combine, the final_id should also be different.

The final_id can be used to identify each unique combination of id1 and value1-4.

The final_id MUST be integer and all values should have the same length of digits, such as 6, 7 or 8 digits.

Any help would be appreciated.

M. A. Kishawy
  • 5,001
  • 11
  • 47
  • 72
user3601704
  • 753
  • 1
  • 14
  • 46
  • 1
    There is something called the pigeon-hole principle that says that this cannot be done reliably: http://en.wikipedia.org/wiki/Pigeonhole_principle. – Gordon Linoff Nov 14 '14 at 22:41
  • Can you possibly add a sample that illustrates your desired output? – Hart CO Nov 14 '14 at 22:46
  • 1
    I agree with Gordon. A `bigint` might be large enough to accommodate this kind of scheme (depending on the range of `id1`) but an `int` is not. I'm curious, though: why do you need to do this? It seems like an odd set of requirements. – Joe Farrell Nov 14 '14 at 22:52

1 Answers1

2

Perhaps I'm missing something, but sounds like a DENSE_RANK() would do:

SELECT id1,value1,value2,value3,value4
      ,DENSE_RANK() OVER(ORDER BY id1,value1,value2,value3,value4)+100000 AS final_ID
FROM YourTable
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • If value1-4 are same but id1 is different, the value_combine will be same if we use ROW_NUMBER(). thanks ! – user3601704 Nov 14 '14 at 22:45
  • @GoatCO . . . I actually think you mean `dense_rank()` and the columns in the `partition by` should all be in the `order by`. I'm still not sure if this really answers the OPs question, but it would at least provide a unique number for each combination. – Gordon Linoff Nov 14 '14 at 22:47
  • @GordonLinoff Indeed, thanks, brain has left the building. Regardless, I seem to have missed what the OP is after anyway. – Hart CO Nov 14 '14 at 22:51