Ranking results in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MGore - 31 Oct 2005 18:41 GMT

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.

Signature

Thank you!

Reply to this Message

Alex Dybenko - 31 Oct 2005 20:53 GMT

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

Signature

Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

> using a table such as:
>
[quoted text clipped - 23 lines]
> the
> P (Price) value.

Reply to this Message

Tim Ferguson - 31 Oct 2005 21:10 GMT

> using a table such as:
>
[quoted text clipped - 6 lines]
> 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

Reply to this Message