Using Row_Number

[luvmy2Js]

I am trying to create a query that numbers my rows and the change of empid, it starts over.. I was looking at some blog on Row_number() but cannot seem to get it to work. Below is my current query, any suggestions would be greatly appreciated!!

EXECUTE adm_Supplimental

SELECT
row_number() over (order by empid) as Row,
k.empid,
'Job Title' = CONVERT(nvarchar, DecryptByKey(p1.[job])),
'Job Start' = CONVERT(datetime, CONVERT(nvarchar, DecryptByKey(p1.[Start Date]))),
'Grade' = CONVERT(nvarchar, DecryptByKey(p8.[Pay Scale Level])),
'Grade Start' = CONVERT(datetime, CONVERT(nvarchar, DecryptByKey(p8.[Start Date])))


from Kims_CompAuditIDs k
inner join ystdy_PA0001 P1 on P1.sapid = k.empid
inner join ystdy_PA0008 P8 on p8.sapid = k.empid

This numbers the rows, but does not start over at the change of empid?

Thanks a million!!!

 

[gmmastros]

If you want the numbering to start over, you need to use the Partition By clause, like this....

row_number() over ([!]Partition By empid [/!]order by empid) as Row

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[luvmy2Js]

I added that and it still didn't work?

EXECUTE adm_Supplimental

SELECT
row_number() over (Partition By empid order by empid) as Row,
k.empid,
'Job Title' = CONVERT(nvarchar, DecryptByKey(p1.[job])),
'Job Start' = CONVERT(datetime, CONVERT(nvarchar, DecryptByKey(p1.[Start Date]))),
'Grade' = CONVERT(nvarchar, DecryptByKey(p8.[Pay Scale Level])),
'Grade Start' = CONVERT(datetime, CONVERT(nvarchar, DecryptByKey(p8.[Start Date])))


from Kims_CompAuditIDs k
inner join ystdy_PA0001 P1 on P1.sapid = k.empid
inner join ystdy_PA0008 P8 on p8.sapid = k.empid

 

[luvmy2Js]

Nevermind, I got it!! Thanks a bunch!!