Orange,
I had a little bit of a tough time following your improved speed post and the original post. I think that guy had several groupings and rankings combined. I put this together from what I found in your post:
Code:
SELECT RankingQuery.pID, RankingQuery.CategoryRank
FROM (SELECT Dupe1.pID, COUNT(*) as CategoryRank
FROM q_AllSub AS Dupe1 INNER JOIN q_AllSub AS Dupe2
ON (Dupe1.pID = Dupe2.pID)
GROUP BY Dupe1.pID
) AS RankingQuery;
And this gave me the correct number of records, the same as my sq_OneSubEach gave me. Although I dont understand why the CategoryRank has weird numbers, several 1s but other numbers like 196, so I am not sure what COUNT(*) is actually counting, could someone explain that to me?
That query gives me the right number of records but does not contain any of the important information so I next tried to add in the sID which is the primary key for the submissions table, this was my attempt:
Code:
SELECT RankingQuery.pID, RankingQuery.sID, RankingQuery.CategoryRank
FROM (SELECT Dupe1.pID, Dupe1.sID, COUNT(*) as CategoryRank
FROM q_AllSub AS Dupe1 INNER JOIN q_AllSub AS Dupe2
ON (Dupe1.pID = Dupe2.pID)
GROUP BY Dupe1.pID, Dupe1.sID
) AS RankingQuery
ORDER BY RankingQuery.sID;
Which I believe the group by is screwing with what is displayed and it returns way more records. I then tried adding in the code:
Code:
WHERE (((RankingQuery.CategoryRank)=1))
and it gave me less records than I have in my original query.
I tried thinking about that max query idea and I went back to the basics and tried to use pID as my group by and then get the Max of Due and that returns the right number of records, but I need the sID associated with each of those records and if I add sID to the query it becomes a group by and ruins what records are returned.
Code:
SELECT Project.pID, Max(Submission.Due) AS MaxOfDueFROM Project INNER JOIN Submission ON Project.Tracking_num = Submission.sTracking_num
GROUP BY Project.pID;
Any direction on either of these queries would be appreciated.
Thank you