Re: Calculating relative record numbers

I simplified the query, indeed, as example, I removed any reference to
[judge contest] = 30, since the OP supplied a wanted result where the [judge
contest] values where all equal to 12. Sure, I should have included the
condition on judge contest too, if there are more than a single contest. If
there is more than one judge by contest, a select distinct is required...
but if a judge is listed just once per contest, the query could be
simplified, though, to:


SELECT a.[judge id], a.[judge contest], 1+COUNT(b.[judge id])
FROM myTable AS a LEFT JOIN myTable AS b
ON a.[judge contest]=b.[judge contest] AND a.[judge id] > b.[judge id]
GROUP BY a.[judge id], a.[judge contest]


where the rank occurs by contest.

Vanderghast, Access MVP

"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:snlok3hpgc0gsmc59lk4kptce1l9ou5j3e@xxxxxxxxxx
Michel Walsh wrote:

If you prefer doing it through a join rather than through a sub query:


SELECT a.[judge id], a.[judge contest], 1+COUNT(b.[judge id])
FROM myTable AS a LEFT JOIN myTable AS b
ON a.[judge id] > b.[judge id]
GROUP BY a.[judge id], a.[judge contest]


Michel,

I may not be seeing all the replies in this thread so my
view of the conversation might be distorted.

I think Steve's unusual ranking and the use of criteria
would require that to be:

SELECT a.[judge id], a.[judge contest],
1+COUNT(b.[judge id])
FROM myTable AS a
LEFT JOIN (SELECT DISTINCT
[judge id], a.[judge contest]
FROM myTable) AS b
ON a.[judge id] > b.[judge id]
And a.[judge contest] = b.[judge contest]
GROUP BY a.[judge id], a.[judge contest]
WHERE a.[judge contest] = 30

Even if I have that right, I'm not sure it will work because
of the [ ] in the subquery. It would probably better to use
a separate query instead of a subquery?

There is also the trade-off between query performance and
the non-equi join requiring an inexperienced person to work
in SQL view. I am struggling with the quandary of providing
something an OP can understand and providing a good example.

--
Marsh
MVP [MS Access]


.

Relevant Pages