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 |
|
jeff06
Posting Yak Master
166 Posts |
 Posted - 2007-03-19 : 11:38:58
|
| I have a tablemerchant contractbr datea 2333 1/1/2005a A34 3/12/2006a R78 2/1/2005..bbbccc.....different merchant has different number of contracts.I want to order the contracts for merchants according to the date.the result table should look like:merchant contractbr date ordera 2333 1/1/2005 1a R78 2/1/2005 2a A34 3/12/2006 3..b 1b 2b 3c 1c 2c 3c 4c 5.....Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-19 : 11:43:20
|
| select *, row_number() over (partition by merchant order by date) as [order]from table1Peter LarssonHelsingborg, Sweden |
 |
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-03-19 : 12:03:10
|
| Thank you for your quick responsewhen I run this code i got error msg:Server: Msg 195, Level 15, State 10, Line 1'row_number' is not a recognized function name.How to fix this problem?Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-19 : 12:06:13
|
| Oh, you are using SQL Server 2000?select t1.*, (select count(*) from table1 as x where x.merchant = t1.merchant and x.date <= t1.date) as [order]from table1 as t1Peter LarssonHelsingborg, Sweden |
|
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-03-19 : 12:24:46
|
| Thank you so much, Preso. It works perfectly.I have one more question in a new thread. I will appreciated if you can help me out. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
how to order this table.