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
