Insert numeric ID with interval

Question

SSCommitted

Points: 1819

April 6, 2007 at 10:59 am

Hi there,

I have a table without unique IDs, and now I want to create a unique numeric ID in a rather special way. The table looks like this right now,

And I need something like this,

How do I create this kind of unique ID?

All help is appreciated

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 1

select

areacode,

areaname ,

unique_id = areacode * 100 + rank() over(order by areacode, areaname)

from table1

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 2

Thanks Mark,

It works !

But unfortunately I need the two last digits in the unique_id to swith to/begin with 01 after every new AreaCode.

Your SQL gives the last digits up to 99, instead of starting with 01 with a new AreaCode.

How do I put AreaCode in as a "resetter"?

Thanks again

Joejoe

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

Try

SELECT A.AreaCode, A.AreaName,

A.AreaCode * 100 + (SELECT COUNT(*) FROM tablename Z WHERE Z.AreaCode = A.AreaCode AND Z.AreaName <= A.AreaName)

FROM tablename A

ORDER BY A.AreaCode, A.AreaName

K. Matsumura

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 4

Thanks Matsumura it works perfect! Just what I needed. Now I have a unique ID which can be order according to areas.

Joejoe