Hello, this is my first post here, i hope i'll find this forum usefull.
Although i did a search about my question, i didnt find what i need , because the question isnt exactly what it sounds.
Here's my scenario
I have a table that doesnt have an identity column, but does have unique rows depending on a combination of two columns.
the table's name is carowner and the two columns i am talking about are iteid,cusid and they are both of datatype int. what i need to do is to add to the existing data of the table a linenumber per iteid. for example i could have the following two records
iteid | cusid
6668 | 1
6668 | 2
6669 | 1
6669 | 4
what i wanna do is add a column so that the above data would look like this
iteid | cusid | Linenum
6668 | 1 | 1
6668 | 2 | 2
6669 | 1 | 1
6669 | 4 | 2
So i need to number the records depending on the column iteid and i need to do that for the existing data but also for every new record inserted into the table.
I was thinking this could be done by adding a trigger to the table but i am not sure on how to do this.
Any help woould be greatly appriciated, so many thanx in advance.
Try this - declare @t table(itemid int,cusid int)
-
insert into @t
-
select 6668,1 union all
-
select 6668,2 union all
-
select 6669,1 union all
-
select 6669,4
Query:(Sql server 2005+) - select
-
t.*
-
,ROW_NUMBER() over(partition by itemid order by itemid) Linenum
-
from @t t
Query (For Sql Version less than 2005) - select *, ( select count(*)
-
from @t t2
-
where t2.itemid = t1.itemid
-
and t2.cusid <= t1.cusid
-
) as Linenum
-
from @t t1
Output: itemid cusid Linenum - 6668 1 1
-
6668 2 2
-
6669 1 1
-
6669 4 2
-
Hope this helps
2 2823
Try this - declare @t table(itemid int,cusid int)
-
insert into @t
-
select 6668,1 union all
-
select 6668,2 union all
-
select 6669,1 union all
-
select 6669,4
Query:(Sql server 2005+) - select
-
t.*
-
,ROW_NUMBER() over(partition by itemid order by itemid) Linenum
-
from @t t
Query (For Sql Version less than 2005) - select *, ( select count(*)
-
from @t t2
-
where t2.itemid = t1.itemid
-
and t2.cusid <= t1.cusid
-
) as Linenum
-
from @t t1
Output: itemid cusid Linenum - 6668 1 1
-
6668 2 2
-
6669 1 1
-
6669 4 2
-
Hope this helps
Nbiswas, this was just what i needed, worked like a charm!
Thanks a lot for your help :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gunnar Vøyenli |
last post by:
Hi
(SQL Server 2000)
I have an existing table (t) with a column that is NOT an identity column
(t.ID), but it has manually inserted "row numbers". I want to make this
column become an identity...
|
by: grzes |
last post by:
MS SQL Server 2000.
My case is: I have the table T with primary key calling __recid int
without identity property. This table includes a lot of records (about
1000000). I need to convert __recid's...
|
by: JJA |
last post by:
I have fixed length records (167 bytes) in a .txt file and want to load
this data exactly as is into a staging table where I hope to be able to
later get at selected columns using the SUBSTRING...
|
by: valerio |
last post by:
Hi all,
I've some problem to import data to DB2 using the db2move and db2look
tools.
Follow the problem :
I have exported data from db2 v. 7 database on windows server, using
the db2look and...
|
by: dusty |
last post by:
Hi, I'll try to simplify the problem:
I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work...
|
by: smauldin |
last post by:
Creating a table with an identity column works fine
create table test(a integer, id integer generated always as identity )
When I attempt to add an identity column it fails.
create table...
|
by: shorti |
last post by:
Can anyone explain in greater (and more comprehensive) detail what the
RESTART option does in the ALTER TABLE table ALTER COLUMN statement.
This is the description in Info Center:
RESTART or...
|
by: bbawa1 |
last post by:
Hi,
I have a table in which I have four columns.
ID (primary Key)
FirstName,
LastName,
Email
I want to add another column as @@identity in this already existing
|
by: ak1dnar |
last post by:
Hi this is my first visit to the MSSQL forum with a question.
Let me explain the scenario,
I have a table say clients table with the structure like id,foo,etc.. and lots of records on it. But...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |