GroupID Order by another Column Exculding 0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GroupID Order by another Column Exculding 0

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2015-05-08 : 06:49:21

hi,

I got a table with column GroupID. Where as the GroupID number will be repeated for specific column and some may be 0.
Need to re-Order the GroupID with unique Id for respective Old GroupID.

TableA:

[ITEM] [SUBITEM] [GROUPID]
PEN A 1
PENCIL A 1
INK PEN A 1
ERASER A 1
SCALE A 1
PEN B 1
PENCIL B 1
INK PEN B 2
ERASER B 3
SCALE B 4
PEN C 1
PENCIL C 2
INK PEN C 3
ERASER C 4
SCALE C 5
PEN D 1
PENCIL D 0
INK PEN D 0
ERASER D 2
SCALE D 3

Required Output:

[ITEM] [SUBITEM] [GROUPID] [NEWGROUPID]
PEN A 1 1
PENCIL A 1 1
INK PEN A 1 1
ERASER A 1 1
SCALE A 1 1
PEN B 1 2
PENCIL B 1 2
INK PEN B 2 3
ERASER B 3 4
SCALE B 4 5
PEN C 1 6
PENCIL C 2 7
INK PEN C 3 8
ERASER C 4 9
SCALE C 5 10
PEN D 1 11
PENCIL D 0 0
INK PEN D 0 0
ERASER D 2 12
SCALE D 3 13

You can see the new groupID contains uniqueID for each group.
Help me to fix this Issue.

Regards,
Kalai

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-08 : 09:31:23

Not following the logic here. For example, in the original we have (PENCIL, A, 1) and (PENCIL, B, 1) (in the same group) but in the new version we have (PENCIL, A, 1,1) and (PENCIL, B, 1,2)

Why is the new group id for (PENCIL, B) different? You required 'GroupID with unique Id for respective Old GroupID.' but that is not what your sample result shows.

Gerald Britton, MCSA
Toronto PASS Chapter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources