Ranking items by date 2

[gemoon]

I am trying to produce the following format which can then be exported into a graphing program:

----Date1------ ---Date2-------
Rank Type Total Type Total

My starting point is qryTotals:

qryTotal
Date Total Type
Date1 2 x
Date1 6 y
Date1 3 z
Date2 20 x
Date2 5 y
Date1 10 z

I'm fairly sure the first step is to generate the following query which ranks Total within each Date:

qryRank
Date Total Type Rank
Date1 2 x 1
Date1 3 z 2
Date1 6 y 3
Date2 5 y 1
Date2 10 z 2
Date2 20 x 3

I can produce rank values for all of my records with the following query

SELECT A.Total, A.Type, Count(*) AS Rank
FROM qryTotal AS A INNER JOIN qryTotal AS B ON A.Total<=B.Total
GROUP BY A.Total, A.Type;

The thing I am stuck on is how to rank within each date. It seems like I should insert (SELECT Count(*) FROM qryTotal….), but I am not sure to put for the WHERE. Can anyone offer some advice?

Thanks,
Ed

 

[mp9]

This may help move you forward, though I'm still not sure if it's exactly what you want:

SELECT A.Total, A.Type, Count(*) AS Rank
FROM qryTotal AS A INNER JOIN qryTotal AS B ON (A.Total<=B.Total AND A.[Date]=B.[Date])
GROUP BY A.Total, A.Type;

This is just an extension of your query, though that (and this revision) seem to rank the wrong way round to how you want.

Hope his helps.

 

[gemoon]

mp9,

Thanks so much for your reply. Sorry for the late response, but I was pulled off of this problem for a few weeks.

Your addition was just what you I needed. Also you were right that the order is not correct. I misstated above that I was looking for a low to high ranking. Actualy I am looking for a high to low ranking with the highest total having a ranking of 1.

The problem I am having now is how to deal with ties. The query now assigns ties with the position of the last ranking like this

Rank
1
2
4
4
5

I need ties assigned the value of the first position like this

Rank
1
2
3
3
5

If I take the = sign to get A.Total < B.Total, I do get the ties assigned the number of the fist tie in the group, but the first record in each A.Date is missing

Another option that I found was to use the following

SELECT A.Date, A.Type, A.Total, (SELECT Count(*) FROM qry_Total
WHERE Date = A.Date
AND Total > A.Total)+1 AS Rank
FROM qry_Total AS A
ORDER BY A.Date, A.Total DESC;

However, the new query is so slow that it is unusable. I have over 3000 records and the query takes over an hour to run. The original query with the inner join is much faster.

\

 

[PHV]

Typed, untested:
SELECT A.Total, A.Type, [!]1+[/!]Count(*) AS Rank
FROM qryTotal AS A [!]LEFT[/!] JOIN qryTotal AS B ON (A.Total[!]<[/!]B.Total AND A.[Date]=B.[Date])
GROUP BY A.Total, A.Type

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

 

[Hosacans]

I'm also running into this problem... i would also like to rank records based on two different fields. however, i want to rank them in one continuous sequence.

Taken from qemoon's example:

qryRank
Date Total Type Rank
Date1 2 x 1
Date1 3 z 2
Date1 6 y 3
Date2 5 y 1
Date2 10 z 2
Date2 20 x 3

instead i want to have a result set looking like:

qryRank
Date Total Type Rank
Date1 2 x 1
Date1 3 z 2
Date1 6 y 3
Date2 5 y 4
Date2 10 z 5
Date2 20 x 6

by compairing Date1 to Date2

please help..

Thanks
H

 

[PHV]

Hosacans, please, start a new thread with YOUR table structure.

 

[gemoon]

PHV,
Thanks so much for your help. It looks like I am almost there. I tried your solution. It does assign the ranks correctly for the ties, but it assigns a rank of 2 to both the first and second rankings so I would get the something like the following.

Date Total Rank
Date 1 100 2
Date 1 50 2
Date 1 30 3
Date 1 20 4
Date 1 20 4
Date 1 10 6
Date 2 50 2
Date 2 40 2
Date 2 30 3
Date 2 20 4
Date 2 10 5
Date 2 10 5
Date 2 5 7
Date 2 1 8
Date 2 1 8

The direction I am now in is to do three separate queries. One selects the highest value and assigns the rank to 1. The second selects all values except for the highest and ranks them 2+. I then used a union query to combine the two.

Assign rank 1 to records with highest total by date, qry_Rank-a:

SELECT qry_Total.Date, Max(qry_Total.Type) AS Type, First(qry_Total.Total) AS Total, 1 AS Rank
FROM qry_Total
GROUP BY qry_Total.Date;



Rank 2 and above by date, qry_Rank-b:

SELECT A.Date, A.Total, A.Type, 1+Count(*) AS Rank
FROM qry_Total AS A INNER JOIN qry_Total AS B ON (A.Date=B.Date) AND (A.Total<B.Total)
GROUP BY A.Date, A.Type, A.Total
ORDER BY A.Date, A.Total DESC , A.Type;


Union query, qry_RankTotal:

SELECT [Date], [Type], [Total], [Rank]
FROM [qry_Rank-a]

UNION SELECT [Date], [Type], [Total], [Rank]
FROM [qry_Rank-b]
ORDER BY [Date], [Rank], [Type];

The problem I now face is how do I deal with ties in qry_Rank-a. As I have it, I only get the first of the records with a max Total in each date. I would like to assign all ties for first place at each date with a ranking of one. Currently, this is not a big issue because there are not ties that I can see, but as the dataset grows, this could be a real problem.

Thanks again for all the help,
Ed

ps. (I apologize if have made typo's here. qry_Total, Type and Total are not from myt db. When I first posted, I thought it would be best to simplify the names. I didn't expect to have all of this cutting and pasting. The real names are qry_preRank, Species and Abundance. The data tracks the response of aquatic animals to reductions in pollution by a wastewater treatment plant.)

 

[Hosacans]

gemoon,

since i am a beginner with access, i am nowhere qualified to give advices, but i think i might have the code that does what you want. I am also working on something similar to your "ranking task".

This is the SQL that i have on my query, and i subbed my field names with yours. the idea is to have 2 instance of the same table and compare them with one another. this code will first compare your date and then rank the records with the same date by comparing Total.

once again i am a beginner to access so i am still not completely sure on the logic behind this. but maybe this is usable for you.

SELECT T1.Date, T2.Total, T3.Type, Count(*) AS Rank
FROM myTable AS T1, myTable AS T2
WHERE (((T1.Date)>=[T2].[Date]) AND ((T1.Total)<=[T2].[Total]))
GROUP BY T1.Date, T1.Total, T1.Type
ORDER BY T1.Date, T1.Total, T1.Type, Count(*);

ironically, this is the result that i DONT want. as i wrote in my previous post, i want to continuously rank the records by comparing two criteria.


Thanks
Hosacans

 

[PHV]

gemoon, sorry for the typo (still untested):
SELECT A.Date, A.Total, A.Type, 1+Count(B.Date) AS Rank
FROM qryTotal AS A LEFT JOIN qryTotal AS B ON A.Total<B.Total AND A.Date=B.Date
GROUP BY A.Date, A.Total, A.Type

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

 

[gemoon]

PHV,

That is it! Thank you so much. Also, thanks to Hosacans for your explanation of there being two instances of the same table. I am fumbling in the dark here and your explanation was enlightening.

Thanks,

Ed