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 |
|
edohD
Starting Member
3 Posts |
 Posted - 2008-10-06 : 14:37:02
|
| Hi, This is my first post.I have been a developer for quite a long time. This time I think I might not be able to avoid cursor.I have a unique scenario here it is:I have massive data resulted from a select statement that looked like this:select ...from(select ...from dbo.SomeFunction(param1, param2...)unionselect ...from dbo.SomeFunction(param1, param2...)unionselect ...from dbo.SomeFunction(param1, param2...)......)order by DateField, field2, field3... etc.I need to add a rank per each DateField of row of data. so for each date, I need to rank a certain sales associate according to order by criteria... how do I do that?the only thing I came up with is using a cursor and loop through it and check dates as it goes. Since this is going to be millions of rows I don't want to use temp tables or table variables.Does anyone know if there is a way to rank this darn thing? like with having or some other stuff that I don't know of?Sorry I can't modify the select statement... Thanks for reading. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 14:52:50
|
Are you using SQL Server 2005? Have a look at the ROW_NUMBER, DENSE_RANK and other windowed functions.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
edohD
Starting Member
3 Posts |
Posted - 2008-10-06 : 14:55:35
|
| you are right, I am using sql server 2005 and will soon upgrade to 2008 but I came from a sql 2000 background. I'll look into the Row_Number and Dense_Rank. Thanks for the help |
|
|
|
edohD
Starting Member
3 Posts |
Posted - 2008-10-06 : 15:01:16
|
| that was it, thanks man. |
|
|
|
|
|
|
Whether cursor can be avoided in my case