add a consequtive numberID to sorted fields
-
table:
chem_id prop-id coef_id rec_disp_id [conseq number]
1 HVP A 1
2 HVP A 2
3 HVP A 3
4 HVP A 4
5 HVP A 5
7 HVP A 6
8 HVP A 7
9 HVP A 8
11 HVP A 9
11 HVP B 10
12 HVP A 11
13 HVP A 12
14 HVP A 13
151 MVP A 14
172 OVP A 15
172 OVP B 16
actually it has only 3 flds now: chem_id, prop_id, and coeff_id. The data is sorted (and shown here) by those 3 fields in the order they appear in the previous sentence.
How would i go about adding a rec_disp_id (as a consequtive integer) to those 3 fields sorted by chem_id, prop_id, and coeff_id ? The result I need is shown above.
Thanks,
-
If records are deleted, the consecutive integers will have gaps. Will this still fit your needs?
If gaps are not a problem, you can run ALTER TABLE. Here is a quick example:
CREATE TABLE #SurveyReport(
PersonID int,
FirstName varchar(50),
LastName varchar(50) 
INSERT INTO #SurveyReport VALUES( 1, 'Joe', 'Smith')
INSERT INTO #SurveyReport VALUES( 2, 'Jane', 'Smith')
INSERT INTO #SurveyReport VALUES( 3, 'Bob', 'Smith')
INSERT INTO #SurveyReport VALUES( 22, 'Barb', 'Smith')
INSERT INTO #SurveyReport VALUES( 55, 'Somebody', 'Else')
ALTER TABLE #SurveyReport ADD Consecutive integer IDENTITY(1,1)
SELECT * FROM #SurveyReport
DROP TABLE #SurveyReport
I wasn't born stupid - I had to study.
-
Farrel: sorry yr way did not work - I have too many recs (22k) to be able to insert them manually.
to accomplish the task, I had to do the following:
1. added an identity column to the src tbl:
alter table _Tbl add Unique_id int identity (1,1)
2. created a temp tbl:
create _Tbl_temp (
rec_disp_id identity (1,1),
unique_id int)
3. inserted unique_id into the temp table in the desired order:
insert _Tbl_temp (unique_id) select coef_set_unique_id
from _Tbl
order by propertyid, chemid, coef_set_id
4. then added field 'rec_disp_id' to original table and updated 'rec_disp_id' by joining it to _Tbl_temp via unique_id
done
Does anyone know an easier way? maybe using some function?
-
correction: step 3 sh've read:
3. inserted unique_id into the temp table in the desired order:
insert _Tbl_temp (unique_id) select unique_id
from _Tbl
order by propertyid, chemid, coef_set_id
-
I just used the #TempTable for an example. You should be able to use the ALTER statement directly upon your existing table. One note: you can do this in Enterprise Manager as well, but I have found that it is much quicker to do it through Query Analyzer.
I wasn't born stupid - I had to study.
-
I'm not sure if this is what you want and I can't seem to find the link to the post right now that discussed (what seems to be) the exact same thing...maybe you could search the posts...but it was something like this:
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
SELECT *, IDENTITY(INT,1,1) AS RowNum
INTO #Results
FROM table
where ....
ORDER BY .....
SELECT *
FROM #Results
ORDER BY RowNum
**ASCII stupid question, get a stupid ANSI !!!** -
Farrel:
I've tried to add an identity (1,1) column to the source tbl, but the numbers do not get assigned according to the sort I'd need. They are assigned in random order.
Thanks
Sushila's way seems to be promising. I'm gonna try it.
-
SELECT *, IDENTITY(INT,1,1) AS RowNum
INTO #Results
FROM table
where ....
ORDER BY .....
SELECT *
FROM #Results
ORDER BY RowNum
the above works just fine. Thanx a bunch, Sushila!
-
Glad it works!
**ASCII stupid question, get a stupid ANSI !!!** -
Dynamically ordering rows in a recordset.
no temp table.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133
I usually try to get the UI Guys to do it in the presentation layer.
but don't always win that battle.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply