2005 vs 2000

[jacob94]

What would the equivalent to the following function be in sql server 2000.

USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity DESC) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO


I am trying to come up with a view that does the following: If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. There can be up to 6 different sorts and that is where I have trouble.

 

[SQLDenis]

are you running SQl SERVER 2000?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

I got this to run. Yes sql server 2000:

drop table Table_TestOnly,#Ranks
create table Table_TestOnly

(field1 varchar(10), wins int, diff int, pct int, pts int)



insert into Table_TestOnly values('admin14', 10, 0, 41, 77)
insert into Table_TestOnly values('admin11', 10, 1, 42, 777)
insert into Table_TestOnly values('admin12', 10 , 24, 41, 4443)
insert into Table_TestOnly values('administr', 10, 24, 62, 50)
insert into Table_TestOnly values('admin14a', 10, 0, 41, 77)
insert into Table_TestOnly values('admin11a', 10, 1, 42, 777)
insert into Table_TestOnly values('administra', 10, 24, 62, 50)


SELECT IDENTITY(int, 1,1) AS Rank ,field1,pct
INTO #Ranks FROM Table_TestOnly WHERE 1=0



INSERT INTO #Ranks (field1,pct)
SELECT field1,diff FrOM Table_TestOnly
ORDER BY wins desc, diff, pct desc, pts desc



select case when r.Rank = (select max(Rank) from #Ranks) then r.Rank
else z.Ranking end Ranking,t.* from (
SELECT z.Ranking ,t2.pct,field1
FROM (SELECT MIN(t1.Rank) AS Ranking,t1.pct FROM #Ranks t1 GROUP BY t1.pct) z
JOIN #Ranks t2 ON z.pct = t2.pct
) z join Table_TestOnly t on z.field1 =t.field1
join #Ranks r on z.field1 =r.field1
and t.diff = z.pct
ORDER BY r.Rank,z.Ranking

 

[jacob94]

it seemed like it worked and that is awesome. One last question and this may be difficult.

I have another table called configs that stores six different sort options.

the are called OrderBy1, OrderBy2 etc.

I need to somehow dynamically sort/rank based on what settings are in the six options.

Any idea on how to do this?

Also, there are two more columns I need to add to the example:

GroupID and Week

How would I set the OrderBy1 - 6 as a variable and have it change as it ran.

 

[jacob94]

the ranking needs to reset based on group id then week