Column Ranking on SS 2000 and/or Reporting Services report

I see a Rank function on SQL Server 2005 and this is exactly what I want. I am writing a Reporting Services report on Sql Server 2000 and the Rank function is not available on either software. The following is my data and what I want...

Name Value Rank

A 5 2

B 5 2

C 6 1

D 4 4

E 2 5

F 1 6

Any ideas on how to code a rank function on SS 2000 or Reporting Services?This article describes using a table-valued function to perform ranking. http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954?pf=true|||

The SQL function in the link may not provide you the result you are looking for, as in SQL Server 2005. For example, the function may return records with ranks like 1,2,3,3,4,5 but what you need is 1,2,3,3,5,6 as per sql 2005. To do that, write an update statement at the end of the function before returning.

UPDATE t1

SET Rank = Rank +

(SELECT SUM(COUNT(Rank)-1) FROM table1 t2 WHERE t2.Rank < t1.Rank

GROUP BY Rank HAVING COUNT(Rank) > 1)

FROM table1 t1

GROUP BY Rank

HAVING COUNT(*) = 1

ORDER BY Rank'

Shyam

|||Thanks. I noticed the result set from the link. Thanks for the code.|||

Can you please mark it as answer?

Shyam

Column Count Percentage of Not Null fields

Hello folks,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'

I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

Thanks for your help
Mikelet me get this straight -- you don't really know which columns the table will have?|||Do you want to inspect the content of each column in each row OR
Is your intention instead to find the number of columns in a table that do not allow nulls?

select object_name(id),colcount=count(*)
,notnullablecount=sum(case when isnullable=0 then 1 else 0 end)
,nullablecount=sum(case when isnullable=1 then 1 else 0 end)
,'Notnullable%'=convert(int,sum(case when isnullable=0 then 1 else 0 end)*100./count(*)+.5)
from syscolumns
where id=object_id('myTable')
group by id