Reset Row_Number() against column

Question

Post reply

Reset Row_Number() against column

gulzarahamed.a

SSC Journeyman

Points: 78
More actions
March 15, 2011 at 8:03 am

#236754

create table t1(ename varchar(50),deptname varchar(50))
insert into t1 values('abc1','sales')
insert into t1 values('abc2','sales')
insert into t1 values('abc3','sales')
insert into t1 values('abc4','account')
insert into t1 values('abc5','account')
insert into t1 values('abc6','account')
insert into t1 values('abc7','admin')
insert into t1 values('abc8','admin')
insert into t1 values('abc9','admin')
i need result as follows
rownumber enamedeptname
-------------------------------------------------
1abc1sales
2abc2sales
3abc3sales
1abc4account
2abc5account
3abc6account
1abc7admin
2abc8admin
3abc9admin

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Looks a lot like homework.

select ROW_NUMBER() over (partition by deptname order by deptname), ename, deptname from t1 order by ename

You should read up on the ROW_NUMBER function and by all means you should do your best to understand what this doing and not just copy and paste.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95461 More actions · Answer 2

This article should help you out - both with understanding, and coding: SQL Server Ranking Functions[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Wayne it was your signature I was thinking of as I was adding my reply. Applies 100% in this case.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95461 More actions · Answer 4

Sean Lange (3/15/2011)
Wayne it was your signature I was thinking of as I was adding my reply. Applies 100% in this case.

😀

(You didn't think of my article for explaining it? :()

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

WayneS (3/15/2011)
Sean Lange (3/15/2011)
Wayne it was your signature I was thinking of as I was adding my reply. Applies 100% in this case.
😀
(You didn't think of my article for explaining it? :()

/me hangs his head in shame. :blush:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/