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.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2014-07-14 : 11:43:33
|
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:item_no seq_no note doc_no ABC 1 blah 1 ABC 2 blahh 1 ABC 3 bla3 1 XYZ 1 more n 2 XYZ 2 another 2 EFG 1 blahhh 3.... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-14 : 11:46:28
|
ROW_NUMBER() OVER (PARTITION BY Item_No ORDER BY ... ) AS Seq_No
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-14 : 11:51:31
|
| Are you looking for something like this:CREATE TABLE #C(CUS_NO int,Item_no varchar(5))INSERT INTO #C VALUES (100,'ABC') (400 , 'EFG'), (100 , 'XYZ'), (200 , 'ABC'), (300 , 'XYZ')SELECT *,ROW_NUMBER() OVER (partition by Item_NO ORDER BY Item_NO) ,DENSE_RANK() OVER ( ORDER BY Item_NO) FROM #CORDER BY Item_NO |
|
|
|
|
|
|
|
|
row_number()