Query Rank Starting at 1 not 0

[WildlyHarry]

I am running the following SQL looking for the rank of activity derived from
other queries.

SELECT (SELECT COUNT(*) FROM [old all] T1 WHERE T1.Total> T.Total) AS [Rank
Q3 2007], t.Total
FROM [old all] AS t
ORDER BY t.Total DESC;

The query works. Unfortunately, Rank Q3 2007 starts with 0 for the highest
amount in the Total column. ie 100 = 0, 99 = 1, 98 = 2, etc. I need the
rank to start at 1 for the highest, since 0 does not make sense as a ranking.
Is there anyway to append my SQL to start the Rank Q3 2007 at 1 instead of
0. Thank you in advance for your help.

 

[Douglas J. Steele]

Try

SELECT (SELECT COUNT(*) + 1 FROM [old all] T1 WHERE T1.Total> T.Total) AS
[Rank
Q3 2007], t.Total
FROM [old all] AS t
ORDER BY t.Total DESC;

or

SELECT (SELECT COUNT(*) FROM [old all] T1 WHERE T1.Total>= T.Total) AS [Rank
Q3 2007], t.Total
FROM [old all] AS t
ORDER BY t.Total DESC;

 

[John Spencer (MVP)]

Easiest way is to add 1 to the rank that is returned

SELECT
1+(SELECT COUNT(*) FROM [old all] T1 WHERE T1.Total>T.Total) AS [Rank Q3 2007]
, t.Total
FROM [old all] AS t
ORDER BY t.Total DESC;

You could change the comparison operator to >= from >, BUT that could result
in ties returning the highest number for the tied positions. Something like
1,3,3,4,5,8,8,8,9 instead of 1,2,4,5,6,6,6,9. Or even worse if there was a
three-way tie for first position-you would get 3,3,3 for the rank of the first
three positions.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County