Calculated Column based on the last record

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!

Use 2 tables.

Table 1:

Set concatenated primary key as Type + TypeID identity

Table 2:

Primary Key Identity ID int

Type varchar()

TypeID int

Adamus

|||

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

|||

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";

|||

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?

Adamus

|||

thanks!

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