Ranking in Queries

[Guest]

I am trying to add a ranking expression to my via via subquery. Searching the
forums I have come up with the following but all ranks equal 1.????

The field ASXCode is a name, Traded_12mth is a numeric field and is the
basis on the rank.
The rank should be descending.
Also when there is a tie it should count like as follows;

100 1
200 2
200 2
300 3


Rgds,

Bruce



SELECT ASXCode, Traded_12mth,
(SELECT Count(*)
FROM tbl_MCR_Stats AS X
WHERE X.ASXCode = tbl_MCR_Stats.ASXCode
AND X.Traded_12mth <= tbl_MCR_Stats.Traded_12mth) AS rank
FROM tbl_MCR_Stats

 

[Marshall Barton]

I am trying to add a ranking expression to my via via subquery. Searching the
forums I have come up with the following but all ranks equal 1.????

The field ASXCode is a name, Traded_12mth is a numeric field and is the
basis on the rank.
The rank should be descending.
Also when there is a tie it should count like as follows;

100 1
200 2
200 2
300 3

SELECT ASXCode, Traded_12mth,
(SELECT Count(*)
FROM tbl_MCR_Stats AS X
WHERE X.ASXCode = tbl_MCR_Stats.ASXCode
AND X.Traded_12mth <= tbl_MCR_Stats.Traded_12mth) AS rank
FROM tbl_MCR_Stats

A ranking query will rank those as 1,2,2,4, not 1,2,2,3.

The way you have it, the ranking would be 1,3,3,4. To
correct this use:
AND X.Traded_12mth < tbl_MCR_Stats.Traded_12mth) + 1 AS rank

AFAICS, the rest of the query looks good to me. Are you
sure there are multiple records for each ASXCode? Or should
you remove that portion of the WHERE clause?

 

[Guest]

Thanks Marshall,

The field ASXCode does not have multuples so I have changed as follows;

SELECT tbl_MCR_Stats.Traded_12mth, (SELECT Count(*)
FROM tbl_MCR_Stats AS X
WHERE X.Traded_12mth < tbl_MCR_Stats.Traded_12mth)+1 AS rank,
tbl_MCR_Stats.ASXCode
FROM tbl_MCR_Stats;

This does rank but not quite as I hoped for.

As you mentioned with the adjustment it now ranks as 1,2,2,4 etc. If there a
way to get the counting to 1,2,3 format. If not I can live with it.

Also how do I get the ranking in decending order? i.e. Max value of
Traded_12mth gets a 1.

Rgds,

Bruce

 

[Marshall Barton]

I think you're out of luck on the 1,2,2,3

To reverse the rank numbers, just change the < sign to >

You seem to have left an extraneous tbl_MCR_Stats.ASXCode
in the WHERE clause. Unless you are doing something really
tricky with that, you should get rid of it.