Ranking subquery problem

[Steve S]

I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8



SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;

Thanks for any help

 

[Marshall Barton]

I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8



SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;

SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC

 

[Steve S]

Yes I tried that. Removing "=" starts the ranking at 0,1,2,3,3,5,,, Wait a
minute - I have an Idea.

I set the field Rank to Rank"1+(select........) and it works. thanks for
the help

I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8



SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;

SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC

 

[Steve S]

Well if I had read your sql closer I would have seen that you added 1 also
but in a different place. thanks

Yes I tried that. Removing "=" starts the ranking at 0,1,2,3,3,5,,, Wait a
minute - I have an Idea.

I set the field Rank to Rank"1+(select........) and it works. thanks for
the help

I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8



SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;

SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC

 

[Marshall Barton]

"The devil is in the details."

Almost gotcha there ;-)
But you worked through it, good going.
--
Marsh
MVP [MS Access]

Well if I had read your sql closer I would have seen that you added 1 also
but in a different place. thanks

Yes I tried that. Removing "=" starts the ranking at 0,1,2,3,3,5,,, Wait a
minute - I have an Idea.

I set the field Rank to Rank"1+(select........) and it works. thanks for
the help

Steve S wrote:
I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8

SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;


SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC

 

[Steve S]

The devil and details do go hand-in-hand. I forgot to mention that once I
calculated the RANK I need to update the source table. I used the sql below
but get an error msg: "Operation must use an updateable query". I have read
the HELP screen on this error but can not figure out how to code it correctly.

UPDATE Table1 SET Table1.Rank = (select count(*)+1 from [table1] as x where
x.score > [table1].[score]);

The "(select count(*).... " sql code works as expected when used in a MAKE
TABLE or APPEND TABLE query but not an UPDATE query. I would like to not use
temp tables to get the updating done.

thanks again. Steve

"The devil is in the details."

Almost gotcha there ;-)
But you worked through it, good going.
--
Marsh
MVP [MS Access]

Well if I had read your sql closer I would have seen that you added 1 also
but in a different place. thanks

Yes I tried that. Removing "=" starts the ranking at 0,1,2,3,3,5,,, Wait a
minute - I have an Idea.

I set the field Rank to Rank"1+(select........) and it works. thanks for
the help


Steve S wrote:
I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8


:
SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;


SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC

 

[Marshall Barton]

Unfortunately, an Update query can not contain a subquery,
so we have to find an alternative. In this case you can use
the DCount function:

UPDATE Table1 SET Table1.Rank = DCount("*", "table1", "score

" & table1.score) + 1

BUT, I strongly recommend that you revisit the need to store
these calculated values in the table. You have not
explained why this would be required and since rankings will
change over time, I can't see where this can be anything but
a BAD idea. The ranking numbers can be recalculated any
time you want to display them in a form or report, so
there's no need to rely on number that might be out of date
--
Marsh
MVP [MS Access].

The devil and details do go hand-in-hand. I forgot to mention that once I
calculated the RANK I need to update the source table. I used the sql below
but get an error msg: "Operation must use an updateable query". I have read
the HELP screen on this error but can not figure out how to code it correctly.

UPDATE Table1 SET Table1.Rank = (select count(*)+1 from [table1] as x where
x.score > [table1].[score]);

The "(select count(*).... " sql code works as expected when used in a MAKE
TABLE or APPEND TABLE query but not an UPDATE query. I would like to not use
temp tables to get the updating done.

thanks again. Steve

"The devil is in the details."

Almost gotcha there ;-)
But you worked through it, good going.

Well if I had read your sql closer I would have seen that you added 1 also
but in a different place. thanks

:
Yes I tried that. Removing "=" starts the ranking at 0,1,2,3,3,5,,, Wait a
minute - I have an Idea.

I set the field Rank to Rank"1+(select........) and it works. thanks for
the help


Steve S wrote:
I have a subquery that ranks test scores correctly until there is a
duplicate. How can I modify the query to get the results I need? I got the
subquery code from a post last October. This is a great resource.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8


:
SELECT Table1.Score, (select count(*) from [Table1] as x where x.score >=
[table1.score]) AS Rank
FROM Table1
ORDER BY Table1.Score DESC;


SELECT Table1.Score,
(SELECT Count(*) +1
FROM [Table1] As x
WHERE x.score > [table1].score
) AS Rank
FROM Table1
ORDER BY Table1.Score DESC