SQL Server Performance Forum – Threads Archive
Identity Values in Select Statements
I have a table with values Id GroupId FieldValue11a
21d
31k
41p
52a
62w
72r
83v
93t
103y I. I want a select statement that will give me values
1. id field
2. FieldValue
3. an index value that will start from 2 and goes on incrementing by 2 for FieldValue sorted in Ascending Order The output should be Id GroupId FieldValue indexValue
11a2
52a4
21d6
31k8
41p10
72r12
93t14
83v16
62w18
103y20
II. The second select statement should give an index value based on the GroupId
The fields should be grouped first based on GroupId and then each of these individual groups should be sorted based on FieldValue in Ascending Order and the indexValue should have a number indicating its order in the particular group from id starting from 3 and incrementing by 3
Id GroupId FieldValue indexValue
11a3
21d6
31k9
41p12
52a3
72r6
62w9
93t3
83v6
103y9 Please help!!!!!!!!!!
If you are using sql 2005, then you can use row_number().
Consider this for your second query resultset,
select id,groupid,fieldvalue,
row_number() over (partition by groupid order by groupid,fieldvalue)*3 AS Indexvalue
from yourtable
This select will work for your First query resultset: select id,groupid,fieldvalue,
row_number() over (order by fieldvalue,groupid)*2 as indexvalue from yourtable also read about row_number() for more in BOL
declare @table table
(
[Id ]int,
GroupId int,
FieldValuechar(1)
)
insert into @table
select1, 1, ‘a’union all
select2, 1, ‘d’union all
select3, 1, ‘k’union all
select4, 1, ‘p’union all
select5, 2, ‘a’union all
select6, 2, ‘w’union all
select7, 2, ‘r’union all
select8, 3, ‘v’union all
select9, 3, ‘t’union all
select10, 3, ‘y’ — Query 1
selectt.[Id], t.FieldValue,
indexValue= ROW_NUMBER() OVER (ORDER BY FieldValue) * 2
[email protected] t /*
Id FieldValue indexValue
———– ———- ——————–
1 a 2
5 a 4
2 d 6
3 k 8
4 p 10
7 r 12
9 t 14
8 v 16
6 w 18
10 y 20
*/ — Query 2
selectt.[Id], t.GroupId, t.FieldValue,
indexValue= ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY GroupId) * 3
[email protected] t /*
Id GroupId FieldValue indexValue
———– ———– ———- ——————–
1 1 a 3
2 1 d 6
3 1 k 9
4 1 p 12
5 2 a 3
6 2 w 6
7 2 r 9
8 3 v 3
9 3 t 6
10 3 y 9
*/ KH
Thanks a Lot ranjitjain and khtan. The Query worked fine.
I have 2 more fields in my input table which we sorted and I need these 2 queries to update these fields of the table
i.e. my input table is
declare @table table
(
[Id ]int,
GroupId int,
FieldValuechar(1),
IndexValue1 int,
IndexValue2int
) I want the First query to update the IndexValue1 field of my Table @table and the Second Query to Update IndexValue2 of the table @table Please Help
Can you explain by means of an example what you are trying to update? I don’t understand your requirement here. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
The output of the first query supplied by khtan should update a field say IndexValue1 in the table @table for each [ID] and the second query should update field say indexValue2 of table @table. So after the execution of the query i need a result like Id GroupId FieldValue IndexValue1 IndexValue2 11a23
21d66
31k89
41p1012
52a43
62w186
72r129
83v146
93t163
103y209 which is the same input table with two fields IndexValue1 and IndexValue2 updated
declare @table table
(
[Id ]int,
GroupId int,
FieldValuechar(1),
IndexValue1 int,
IndexValue2 int
)
insert into @table ([Id], GroupId, FieldValue)
select1, 1, ‘a’union all
select2, 1, ‘d’union all
select3, 1, ‘k’union all
select4, 1, ‘p’union all
select5, 2, ‘a’union all
select6, 2, ‘w’union all
select7, 2, ‘r’union all
select8, 3, ‘v’union all
select9, 3, ‘t’union all
select10, 3, ‘y’
updatet
setIndexValue1= v.IndexValue1,
IndexValue2= v.IndexValue2
[email protected] t
inner join
(
selectt.[Id], t.FieldValue,
IndexValue1= ROW_NUMBER() OVER (ORDER BY FieldValue) * 2,
IndexValue2= ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY FieldValue, GroupId) * 3
[email protected] t
) v
ont.[Id]= v.[Id] select* from @table /*
Id GroupId FieldValue IndexValue1 IndexValue2
———– ———– ———- ———– ———–
1 1 a 2 3
2 1 d 6 6
3 1 k 8 9
4 1 p 10 12
5 2 a 4 3
6 2 w 18 9
7 2 r 12 6
8 3 v 16 6
9 3 t 14 3
10 3 y 20 9
*/ KH
Thanks khtan. It was exactly what i wanted!
You are welcome.
KH
]]>