# Calculated Column based on the last record

• ### Question

•

Hi everyone,

I am thinking of this is possible:

ID                    Type             TypeID

1                    car                    1

2                    car                    2

3                    house               1

4                    car                    3

5                    house               2

6                    house               3

7                    car                    4

8                    car                    5

9                    car                    6

10                    house               4

I have identity column (ID), Varchar (Type), Int (TypeID)

I need the TypeID to be an identity for each Type column, incremented the same way as the ID column but values depends on the Type column value.

How can I calculate this?

Thanks!

Monday, May 14, 2007 7:41 PM

• Try:

Code Snippet

create table dbo.t1 (

ID int not null identity,

[Type] varchar(25) not null

)

go

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

go

select

ID,

[Type],

(

select count(*)

from dbo.t1 as b

where b.[Type] = a.[Type] and b.ID <= a.ID

) as TypeID

from

dbo.t1 as a

order by

[Type],

[TypeID]

go

-- SS 2005

select

ID,

[Type],

row_number() over(partition by [Type] order by ID) as TypeID

from

dbo.t1

order by

[Type],

[TypeID]

go

drop table dbo.t1

go

AMB

Monday, May 14, 2007 7:57 PM

### All replies

• Use 2 tables.

Table 1:

Set concatenated primary key as Type + TypeID identity

Table 2:

Primary Key Identity ID int

Type varchar()

TypeID int

Monday, May 14, 2007 7:47 PM
• Try:

Code Snippet

create table dbo.t1 (

ID int not null identity,

[Type] varchar(25) not null

)

go

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('house')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('car')

insert into dbo.t1([Type]) values('house')

go

select

ID,

[Type],

(

select count(*)

from dbo.t1 as b

where b.[Type] = a.[Type] and b.ID <= a.ID

) as TypeID

from

dbo.t1 as a

order by

[Type],

[TypeID]

go

-- SS 2005

select

ID,

[Type],

row_number() over(partition by [Type] order by ID) as TypeID

from

dbo.t1

order by

[Type],

[TypeID]

go

drop table dbo.t1

go

AMB

Monday, May 14, 2007 7:57 PM
• Since this is a derivable value, rather than using a column in the base table consider using an expression to extract the ranking value. In general cases folks use a view or a computed column for such requirements. In t-SQL, here are a few options:

-- #1

SELECT "id", "type",
RANK() OVER ( PARTITION BY "type" ORDER BY "id" )
FROM tbl
ORDER BY "id" ;

--#2

SELECT "id", "type",
( SELECT COUNT(*) FROM tbl t2
WHERE t2.type = t1.type
AND t2."id" <= t1."id" )
FROM tbl t1 ;

--#2

SELECT t1."id", t1."type", COUNT(*)
FROM tbl t1
JOIN tbl t2
ON t2.type = t1.type
AND t2."id" <= t1."id"
GROUP BY t1."id", t1."type"
ORDER BY t1."id";

Monday, May 14, 2007 8:06 PM
• I'd like to comment that although both posted approaches will work, it may be the beginning of a maintenance nightmare. If, in fact, the values will be stored and not derived, 2 tables would support good design and optimize the cognition when alterations on the query are required.

Also, if they are calculated, why not calculate on insert?

Monday, May 14, 2007 8:32 PM
•

thanks!

i created a stored procedure to insert calculated column using the row_number() over(partition by [Type] order by ID) as TypeID

Monday, May 14, 2007 9:00 PM