Ranking results in Access

[Guest]

using a table such as:

Country V1 V2 V3 V4
C1 P1 P2 P3 P4
C2 P1 P2 P3 P4

I need to be able to find the lowest TWO values of "P" for each row "C" and
return the "P" value AS WELL AS its corresponding "V" value. Is there an
easy way to do this and keep it as dynamic as possible (not hardcode the "V"
or "C" values)?

For example, for C1, the lowest two rates might be P4/V4, followed by P2/V2
whereas, for C2, the ranking might be P1/V1, followed by P3/V3. I just want
to see a result set with:
C1 V4 P4 V2 P2
C2 V1 P1 V3 P3

if it's of any value, in excel, I can use:
=INDEX(activevendors,MATCH(E2,$AI2:$AY2,0)) to get the V (Vendor) value and
=SMALL($AI2:$AY2,1) (changing ,1 to be the rank I'm looking for) to get the
P (Price) value.

 

[Alex Dybenko]

Hi,
i think that if you build this table in a normal way, like:

Country vendor Price
C1 V1 P1
C1 V2 P2
etc

then you can easy get lowest 2 P values using Select Top 2

 

[Tim Ferguson]

using a table such as:

Country V1 V2 V3 V4
C1 P1 P2 P3 P4
C2 P1 P2 P3 P4

I need to be able to find the lowest TWO values of "P" for each row
"C" and return the "P" value AS WELL AS its corresponding "V" value.
Is there an easy way to do this and keep it as dynamic as possible
(not hardcode the "V" or "C" values)?

first of all, you need to normalise your table into something like this:


Country VNumber Value
1 1 12
1 2 16
1 3 23
1 4 11
2 1 23
2 2 17
2 3 16
2 4 10

and then a simple correlated subquery will get the required values. Note
that the TOP 3 refers to the two you want and the next one which you
don't. What the query does is to return all rows where the Value is less
than the third highest, for each value of Country.

This should work for any number of Vnumbers in a particular country, but
I haven't tested.

Likely problem: if there is a tie for 2nd-3rd place, the tying second
value is omitted from the result set. This may or may not be what you
want, as it was not mentioned in your problem description.

select
a.country,
a.vnumber,
a.value

from pvalues as a

where a.value < ANY
( select top 3 value from pvalues as b
where b.country = a.country
order by value asc
)



Result set:

country vnumber value
1 1 12
1 4 11
2 3 16
2 4 10


Hope that helps


Tim F