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]

That's is a little complicated in SQL 2000, you have to use a temp table with an identity column and then you have to use the MIN function. An example is here

http://sqlservercode.blogspot.com/2006/03/ranking-in-sql-server-2000.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

sqldenis

can you look at my other thread?

thread183-1239164

 

[SQLDenis]

My resultset would be
rank, pct
1 0
1 0
3 1
3 1
5 24
5 24
5 24



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

Can you help me with the other threads code so it does a rank and not a dense rank?

 

[SQLDenis]

Let me ask you is the result below correct?
1 0
1 0
3 1
3 1
5 24
5 24
5 24

This is difficult to do since you have more than one column to base the result on


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

Execute the code in the other thread:
You will get this as a rank
1
1
2
3
3
4
4

it should be:

1
1
3
3
5
5
7

 

[SQLDenis]

I know I rewrote it and get
1 0
1 0
3 1
3 1
5 24
5 24
5 24

Why should the last row be 7 if the percentage is the same as 5 and 6 (24)?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

change this line:
select * from Table_TestOnly order by wins desc, diff , pct desc

diff should not be desc

 

[jacob94]

for the diff column, lower is better. he did not show that in his code so i changed it.

 

[SQLDenis]

you will have to add another column that will store this cummulative 'score' so that you can order by that

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

Any way you can help with the code. I just can't figure it out, too advanced for me.

I think he has it, just need to do a rank () instead of dense rank ()

 

[jacob94]

can you add a case statement that would rank off the rankfield?

case when rankfield....

to get this?

1
1
3
3
5
5
7

 

[SQLDenis]

Ok this is what I have

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 z.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
and t.diff = z.pct
ORDER BY z.Ranking

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

i get this error:

Column or parameter #0: Cannot find data type INT.

 

[SQLDenis]

okay here we go replace the last select with

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

i still get same error


Column or parameter #0: Cannot find data type INT.

 

[SQLDenis]

so this is the whole script

--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

and this is the output

Ranking     Field1     wins        diff        pct         pts         
----------- ---------- ----------- ----------- ----------- ----------- 
1           admin14    10          0           41          77
1           admin14a   10          0           41          77
3           admin11a   10          1           42          777
3           admin11    10          1           42          777
5           administra 10          24          62          50
5           administr  10          24          62          50
7           admin12    10          24          41          4443

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jacob94]

if i drop the 2 tables and start over i get
Server: Msg 207, Level 16, State 3, Line 17
Invalid column name 'field1'.

 

[jacob94]

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

gives the error
Column or parameter #0: Cannot find data type INT.