none
Calculated Column based on the last record RRS feed

  • 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

Answers

  • 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
    Moderator

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

     

    Adamus

    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
    Moderator
  • 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?

     

    Adamus

    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