SubQuery - Rank of Rows

[VCarrion]

Hi all:

In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:

Spend Rank
24 1
12 2
10 3
9 4

What I wish to accomplish is:

Spend Rank (descending)
24 4
12 3
10 2
9 1


Please let me know what I need to accomplish it.

Thanks for the help

Victor

SELECT
CompanyName, Spend,
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)
AS Rank
FROM
ACC0704
ORDER BY
Spend DESC

[andi_g69]

Replace
ACC0704.Spend <= CoSpendTotal.Spend)
by
ACC0704.Spend >= CoSpendTotal.Spend)

[VCarrion]

Andi:


Thanks that is was I was looking for.

So simple, so beautiful solution.

I really appreciate your input.

Victor

[VCarrion]

Andi and all:

I still have a question.

If both tables are the same why changing the operator from <= to >= has the effect of changing the rank from ascending to descending.

ACC0704 is identical to CoSpendTotal
28 28 1
21 11 2
13 13 3
11 11 4

Thanks for the help.

Victor

[andi_g69]

The subquery
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)

is querying ACC0704, however there is a condition (WHERE...) and an aggregate function (COUNT) which makes the difference.

The logic of the query can be described like this:
For each record in ACC0704 count the number of records where the spend is greater or equal to the spend of the current record.

Turning the >= to <= changes the meaning of the above statement to:
For each record in ACC0704 count the number of records where the spend is less or equal to the spend of the current record.

Hope this makes it a bit clearer

[VCarrion]

Andi:

Thanks for the help.

One additional question, the table copy Cospendtotal is created in memory, right.

Just for information, can I use this table while in memory like any other table created, or I just use it inside the subquery only, with certain limitations.

The subquery concept is new for me and I am trying to understand all his implications in this sample.

Thanks again for the help.

[andi_g69]

The subquery is executed at runtime and the result set is available at execution time of the query. You cannot reference outside your query

[VCarrion]

Andi:

thanks for all your help.

Until next time.

Victor