Re: Calculating relative record numbers

Steve S wrote:

The following SQL selects 8 records from a table [Judges].

SELECT Judges.[Judge ID], Judges.[Contest ID]
FROM Judges
WHERE (((Judges.[Contest ID])=30));

I want to add a column that will also display the relative record number as
shown below.

78 12 1
23 12 2
44 12 3
53 12 4
and so on.

Adding the additional column is not the problem: how to calculate or devise
the relative number is the problem


This is usually called a ranking query and it requires one
or more fields that can be used to produce a unique sorting
ot the records.

Here's an example that assumes you have a field named
SortField that meets that requirement:

SELECT Judges.[Judge ID], Judges.[Contest ID],
(SELECT Count(*)
FROM Judges As X
WHERE X.[Contest ID] = Judges.[Contest ID]
And X.SortField <= Judges.SortField) As Rank
FROM Judges
WHERE Judges.[Contest ID] = 30

If that's slow enough to notice, post back and we'll try
another way.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • RE: Calculating relative record numbers
    ... FROM Judges AS Q ... KARL DEWEY ... "Steve S" wrote: ... I want to add a column that will also display the relative record number as ...
    (microsoft.public.access.queries)
  • Re: Calculating relative record numbers
    ... the relative number sequence starts with 1 and and is sequential. ... I want to add a column that will also display the relative record number as ... This is usually called a ranking query and it requires one ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • Re: Calculating relative record numbers
    ... what I get ia great until a duplicate is found. ... the relative number sequence starts with 1 and and is sequential. ... I want to add a column that will also display the relative record number as ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • Re: Displaying Awards With Model at IPMS NATS ??
    ... as they "tend to influence the judges". ... Any "judge" who would be influenced by such display techniques; ... exhibition is "impressing the general public" with hopes of recruiting new members. ... That means the judges are required to ignore anything other than the model. ...
    (rec.models.scale)
  • Re: No attempt to avoid collision
    ... "Steve" wrote ... Considering the kind of judges that have been appointed over the past couple of decades, I would expect the entire blame to be placed on the speedboat with charges of impeading law inforcement, assulting CG officers, damaging federal property, and terrorism thrown in. ... We're pretty much a police state now. ...
    (rec.boats.cruising)
Loading