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 |
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 2010-04-14 : 23:03:22
|
| I thought maybe the Rank feature would work but it's not quite doing what I was hoping. Maybe there is a diffent way to use it than I'm tryingHere is basically the query on a scaled downSELECT [Account], [Order Number], [Order Date], RANK () OVER (ORDER BY [Order Number]) AS [Order Rank]FROM Order WITH (NOLOCK) ORDER BY [Account], [Order Number]Here's what I'm trying to doThe account is distinct but they may have several orders. I'm doing one table of Sales and one for returns with some date diff to see how many months a product has been out.So in order to do what I'd like ..... I need to list the Account Then Orders Starting With the MIN(Order) with a value of 1and adding +1 for any extra orders..... Now the orders are in a sequential order to a degree but not in a fasion if they have multiple Sales and returnsBasically if they have multiples that wash out I can join on this field later onSo ever Sale Min Order =1 will have a left join to the return table Min Return =1 Hopefully that makes sense. |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2010-04-14 : 23:04:56
|
| Oh I forgot to mention... With one account Rank works well... but when I do a top 100 then the rank doesnt work how I need. Is there another feature that maybe will do the trick ? or is there more with Rank that I can do that I havent found in my reading yet. |
 |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2010-04-15 : 08:48:13
|
| I found my answerSELECT [Account], [Order Number], [Order Date], ROW_NUMBER() OVER(PARTITION BY [Account] ORDER BY [Account],[Order Number]) AS [Work Order Rank]FROM Order WITH (NOLOCK)ORDER BY [Account], [Order Number]This seems to be doing what I need. |
|
|
|
|
|
|
Rank Question