Another ranking by item question.

[Hosacans]

Hi All,

As PHV advised, i start a thread to better discribe my access query problem.
I'm trying to rank style performance by two fields, EOH_U and ST. Please have a look at sample data

Time Cntry_No Chnl_No Loc_No Div_No Style EOH_U ST
1Day 2001 200 0 1002 2007R023RQ3040 183 -5.49450549450549E-03
1Day 2001 200 0 1002 2007R023RQ3152 772 -1.2970168612192E-03
1Day 2001 200 0 1002 2007R023RQ3650 1308 0
1Day 2001 200 0 1002 2007Q023QQ3765 867 0
1Day 2001 200 0 1002 2007Q023QQ3150 746 0
1Day 2001 200 0 1002 2007R023RQ3082 650 0
1Day 2001 200 0 1002 2007Q023QQ3742 615 0
1Day 2001 200 0 1002 0000M023MQ3204 604 0
1Day 2001 200 0 1002 2007R023RQ3109 541 0
1Day 2001 200 0 1002 2007Q023QQ3041 481 0
1Day 2001 200 0 1002 0000M023MQ3203 433 0

*************
I tried to use a ranking query and sort by ST then by EOH_U using this code:

SELECT T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.Style, T1.EOH_U, T1.ST, Count(*) AS Rank INTO Bottom_15
FROM Bottom_Style_Pool AS T1, Bottom_Style_Pool AS T2
WHERE (((T1.Time)=[T2].[Time]) AND ((T1.Cntry_No)=[T2].[Cntry_No]) AND ((T1.Chnl_No)=[T2].[Chnl_No]) AND ((T1.Loc_No)=[T2].[Loc_No]) AND ((T1.Div_No)=[T2].[Div_No]) AND ((T1.ST)>=[T2].[ST]) AND ((T1.EOH_U)<=[T2].[EOH_U]))
GROUP BY T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.Style, T1.EOH_U, T1.ST
ORDER BY T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.ST, T1.EOH_U DESC , Count(*);

...and this is the result set i get.

Time Cntry_No Chnl_No Loc_No Div_No Style EOH_U ST Rank
1Day 2001 200 0 1002 2007R023RQ3040 183 -5.49450549450549E-03 1
1Day 2001 200 0 1002 2007R023RQ3152 772 -1.2970168612192E-03 1
1Day 2001 200 0 1002 2007R023RQ3650 1308 0 1
1Day 2001 200 0 1002 2007Q023QQ3765 867 0 2
1Day 2001 200 0 1002 2007Q023QQ3150 746 0 4
1Day 2001 200 0 1002 2007R023RQ3082 650 0 5
1Day 2001 200 0 1002 2007Q023QQ3742 615 0 6
1Day 2001 200 0 1002 0000M023MQ3204 604 0 7
1Day 2001 200 0 1002 2007R023RQ3109 541 0 8
1Day 2001 200 0 1002 2007Q023QQ3041 481 0 9
1Day 2001 200 0 1002 0000M023MQ3203 433 0 10

But what i really want is the ranking to be continuous. like this:

Time Cntry_No Chnl_No Loc_No Div_No Style EOH_U ST Rank
1Day 2001 200 0 1002 2007R023RQ3040 183 -5.49450549450549E-03 1
1Day 2001 200 0 1002 2007R023RQ3152 772 -1.2970168612192E-03 2
1Day 2001 200 0 1002 2007R023RQ3650 1308 0 3
1Day 2001 200 0 1002 2007Q023QQ3765 867 0 4
1Day 2001 200 0 1002 2007Q023QQ3150 746 0 5
1Day 2001 200 0 1002 2007R023RQ3082 650 0 6
1Day 2001 200 0 1002 2007Q023QQ3742 615 0 7
1Day 2001 200 0 1002 0000M023MQ3204 604 0 8
1Day 2001 200 0 1002 2007R023RQ3109 541 0 9
1Day 2001 200 0 1002 2007Q023QQ3041 481 0 10
1Day 2001 200 0 1002 0000M023MQ3203 433 0 11

The sample data doesnt fully illustrate the ambiguity of the result set. I am working with more than 30000 records... there arent that many duplicate records since it has a couple of "tiers" in the data structure. but once the ST and EOH_U fields becomes more variant, the ranks becomes "unpredictable"

essentially, I am trying to rank each style first by ST (sellthrough) and if two or more styles have the same ST, it should rank each of those styles by EOH_U (End on Hand units), but the "sub" ranking should still be part of the ST ranking.

Please help, i've been stuck on this query for a few days.

Thanks
H

 

[PHV]

And what about this ?
SELECT T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.Style, T1.EOH_U, T1.ST, Count(*) AS Rank
INTO Bottom_15
FROM Bottom_Style_Pool AS T1 INNER JOIN Bottom_Style_Pool AS T2
ON T1.Time=T2.Time AND T1.Cntry_No=T2.Cntry_No AND T1.Chnl_No=T2.Chnl_No AND T1.Loc_No=T2.Loc_No AND T1.Div_No=T2.Div_No
WHERE T1.ST>T2.ST OR (T1.ST=T2.ST AND T1.EOH_U<=T2.EOH_U)
GROUP BY T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.Style, T1.EOH_U, T1.ST
ORDER BY T1.Time, T1.Cntry_No, T1.Chnl_No, T1.Loc_No, T1.Div_No, T1.ST, T1.EOH_U DESC , Count(*);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hosacans]

This works perfect!!

Thanks so much PHV, you just saved me a 7-query work around for this problem.

-H