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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2007-01-30 : 13:30:29
|
| How to code to add sequency number for each group based on date order?name Date orderAAA 1/10/2005AAA 1/15/2005BBB 2/1/2005BBB 2/10/2005BBB 3/1/2005become:sequence name Date order1 AAA 1/10/20052 AAA 1/15/20051 BBB 2/1/20052 BBB 2/10/20053 BBB 3/1/2005 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-30 : 13:37:04
|
| Select them into a Temp Table (using ORDER BY) which has an IDENTITY column?Or upgrade to SQL 2005?!Kristen |
 |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2007-01-30 : 13:55:38
|
| If you add identify column, seq number will increase from 1 to max count, not star over by name group. That is: 1 2 3 4 5, not 1 2 1 2 3 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 14:38:36
|
| select (select count(*) from table1 as t2 on t2.name = t1.name and t2.dateorder <= t1.dateorder) as sequence, t1.name, t1.dateorderfrom table1 as t1Peter LarssonHelsingborg, Sweden |
|
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2007-01-30 : 14:52:55
|
| Peso, what is table1 and t1? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-30 : 14:59:59
|
| After 209 posts, you should know that they are synonyms to the table names YOU use.I can't read minds so I have absolutely no idea what you have named your tables and columns.With the code suggested, I show a technique to use.What you have to do, is to replace the suggested column and table names with the ones in YOUR environment.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 03:56:38
|
| "seq number will increase from 1 to max count, not star over by name group."Sorry, I didn't ready the question carefully.Kristen |
|
|
|
|
|
|
|
|
How to add sequency number in each group?