Defining Ranks within DataGroups

[BusyProfessorFromBremen]

Hi @ all,

I experience a little problem by defining ranks to certain value's in a
table. So far I counted the records within an recordset group through the sql
statement COUNT. Thus similar records will be cummulated and the other will
get the count 0. In the following the problem is listed as an example.

Example:

Original Table:

ID Value
123 4
123 4
123 5
123 6
321 2
321 2

The Outcome should be:

ID Value1 Rank
123 4 1
123 4 2
123 5 3
123 6 4
321 2 1
321 2 2

Any help is highly apreciated. Thank you very much in advance.

Yours Sincerely

Malte

 

[Stefan Hoffmann]

hi,

ID Value1 Rank
123 4 1
123 4 2
123 5 3
123 6 4
321 2 1
321 2 2

Any help is highly apreciated. Thank you very much in advance.

SELECT
O.ID,
O.Value,
(
SELECT Count(*) + 1
FROM Table1 I
WHERE I.ID = O.ID AND I.Value < O.Value
) AS Rank
FROM Table1 O;

Ensure that you have a combined index on ID and Value.


mfG
--> stefan <--

 

[John Spencer]

That ranking is impossible (as far as I Know) to achieve in a query or
series of queries without having some other field to determine which
pair of the matched pairs of values comes first.

You could get sequential values with no break based on that data but you
would still have duplicate numbers where you had duplicate pairs.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================