Counter based only on certain columns

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 2005 Forums

Transact-SQL (2005)

Counter based only on certain columns

Author

Topic

notmyrealname

98 Posts

Posted - 2010-11-03 : 11:14:13

Hi,

I have the following query...


SELECT     Project, Part, Div, ID
FROM         Material_XSteel
WHERE     (Project = N'09-667') AND (Part = N'P1002')

that returns this...


Project Part    Div     ID
------- ------- ------- -------
09-667	P1002	EMD	2498910
09-667	P1002	EMD	2673898
09-667	P1002	EMD	2673950
09-667	P1002	EMD3	4770467
09-667	P1002	EMD3	4770539
09-667	P1002	EMD3	4770611
09-667	P1002	EMD3	4770683
09-667	P1002	EMD3	4770755
09-667	P1002	EMD6	7051298
09-667	P1002	EMD6	7051660
09-667	P1002	EMD8	14139525
09-667	P1002	EMD8	14139599
09-667	P1002	EMD8	14139763
09-667	P1002	EMD8	14139827
09-667	P1002	EMD8	14139891
09-667	P1002	EMD8	14139955
09-667	P1002	EMD8	14140019
09-667	P1002	EMD8	14140083
09-667	P1002	EMD8	14140147
09-667	P1002	EMD8	15125834
09-667	P1002	EMD8	15125900

I would like to add an Index to the results based on the Project, Part, Div fields. What i would like to see is this...


Project Part    Div     ID       Index
------- ------- ------- -------- -------
09-667	P1002	EMD	2498910  1
09-667	P1002	EMD	2673898  2
09-667	P1002	EMD	2673950  3
09-667	P1002	EMD3	4770467  1
09-667	P1002	EMD3	4770539  2
09-667	P1002	EMD3	4770611  3
09-667	P1002	EMD3	4770683  4
09-667	P1002	EMD3	4770755  5
09-667	P1002	EMD6	7051298  1
09-667	P1002	EMD6	7051660  2
09-667	P1002	EMD8	14139525 1
09-667	P1002	EMD8	14139599 2
09-667	P1002	EMD8	14139763 3
09-667	P1002	EMD8	14139827 4
09-667	P1002	EMD8	14139891 5
09-667	P1002	EMD8	14139955 6
09-667	P1002	EMD8	14140019 7
09-667	P1002	EMD8	14140083 8
09-667	P1002	EMD8	14140147 9
09-667	P1002	EMD8	15125834 10
09-667	P1002	EMD8	15125900 11

Does anyone know if my query can do this?

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:24:29

[code]SELECT Project, Part, Div, [ID],
ROW_NUMBER() OVER (PARTITION BY Project,Part,Div ORDER BY [ID]) AS [INDEX]
FROM Material_XSteel
WHERE (Project = N'09-667') AND (Part = N'P1002')

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.