Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Row_Number

Status
Not open for further replies.

luvmy2Js

IS-IT--Management
Sep 1, 2010
43
US
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!!!
 
If you want the numbering to start over, you need to use the Partition By clause, like this....

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top