Avoid a cursor?

[RRinTetons]

I'd hate to attract the scorn of all right thinking SQL folks by resorting to a cursor, but I don't see anyway to avoid it. Any ideas?

Given this data:

Season,CustomerID,TranID,TranLine,Product,Quantity

2008-2009,00111,111111,1,1011,1
2008-2009,00112,111120,1,1011,1
2008-2009,00115,111125,1,1011,1
2008-2009,00115,111125,1,1011,-1
2008-2009,00115,111128,1,1013,1
2009-2010,00111,111199,1,1011,1
2009-2010,00111,111199,1,1011,-1
2009-2010,00111,111200,1,1013,1
2009-2010,00133,111221,1,1011,1

I want to return only the last row for a customer for a given season; the product the customer had as of his/her last transaction for a given season. So, in the case above, I should get:

2008-2009,00111,111111,1,1011,1
2008-2009,00112,111120,1,1011,1
2008-2009,00115,111128,1,1013,1
2009-2010,00111,111200,1,1013,1
2009-2010,00133,111221,1,1011,1

The actual data has more columns and about 150,000 rows.

Easy enough to do with a bunch of variables and walking through it with a cursor. Any better approach?



-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

I'm thinking there may be a way to do this with APPLY...

-
Richard Ray
Jackson Hole Mountain Resort

 

[gmmastros]

If your using SQL2005 or newer, try this?

; With Data As
(
Select *,
       Row_Number() Over (Partition By CustomerId, Season Order By TranId DESC) As RowId
From   [!]YourTableName[/!]
)
Select * From Data Where RowId = 1

If this works, please replace the *'s with your list of column names.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RRinTetons]

I think that works. There's a trivial problem when the last tran was a return (qty = -1), but that's easily addressed in the final WHERE clause.

So the magic line is:

Row_Number() Over (Partition By CustomerId, Season Order By TranId DESC) As RowId

If I read that correctly, the partition is built on CustomerID inside a partition for Season, then it is numbered in descending order, then the outside SELECT gets just the rows numbered 1, which are the last rows by TranID order.

Yes?



-
Richard Ray
Jackson Hole Mountain Resort

 

[gmmastros]

yes

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom