Hi all,

For each group I wants to give an UniqueId like this.


ID UniqueId

3 1001
3 1001

4 1002
4 1002

5 1003
5 1003

6 1004
6 1004

7 1005
7 1005


How can I.

Thanks in advance.

Babu
CREATE TABLE #t (ID int)

INSERT INTO #t VALUES (3)

INSERT INTO #t VALUES (3 )

INSERT INTO #t VALUES (4)

INSERT INTO #t VALUES (4 )

INSERT INTO #t VALUES (4 )

INSERT INTO #t VALUES (5 )

INSERT INTO #t VALUES (5 )

INSERT INTO #t VALUES (5 )

INSERT INTO #t VALUES (5 )

SELECT ID,CASE WHEN ID =3

THEN '1001'

WHEN ID =4

THEN '1002'

WHEN ID =5

THEN '1003'

END UniqueId FROM

#t





"Babu" <u45901@uwe> wrote in message news:898a4e6d14d7f@uwe...
> Hi all,
>
> For each group I wants to give an UniqueId like this.
>
>
> ID UniqueId
>
> 3 1001
> 3 1001
>
> 4 1002
> 4 1002
>
> 5 1003
> 5 1003
>
> 6 1004
> 6 1004
>
> 7 1005
> 7 1005
>
>
> How can I.
>
> Thanks in advance.
>

On SQL Server 2005/2008 you can use DENSE_RANK:

CREATE TABLE Foo (
foo_id INT);

INSERT INTO Foo VALUES(3);
INSERT INTO Foo VALUES(3);
INSERT INTO Foo VALUES(4);
INSERT INTO Foo VALUES(4);
INSERT INTO Foo VALUES(5);
INSERT INTO Foo VALUES(5);
INSERT INTO Foo VALUES(6);
INSERT INTO Foo VALUES(6);
INSERT INTO Foo VALUES(7);
INSERT INTO Foo VALUES(7);

SELECT foo_id,
1000 + DENSE_RANK() OVER(ORDER BY foo_id)
FROM Foo;

--
Plamen Ratchev
http://www.SQLStudio.com

Hi Plamen Ratchev,

It is working fine.

Thaks.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/200809/1