From my research so far, it does not seem like Access does ranking queries very well (what would be OVER PARTITION in other SQL programs). I'm hoping I can figure this out, with a little help!
In the ranking query, I'd like it to rank each record sequentially in field TagScoreOrder. Ultimately I'd like to use that rank to select the Top 10 results. Right now, it is ranking with ties, so selecting Top 10 doesn't really narrow down the query that well.
My initial query looks like this:
qryTagScore
Code:
SELECT tblUserLikedContentMetadata.UserID, tblContentTag.TagID, Count(tblContentTag.TagID) AS TagScore
FROM tblUserLikedContentMetadata INNER JOIN tblContentTag ON tblUserLikedContentMetadata.ContentID = tblContentTag.ContentID
GROUP BY tblUserLikedContentMetadata.UserID, tblContentTag.TagID
ORDER BY tblUserLikedContentMetadata.UserID, Count(tblContentTag.TagID) DESC;
Output data:
UserID |
TagID |
TagScore |
1 |
192 |
3 |
1 |
371 |
3 |
1 |
242 |
2 |
2 |
358 |
3 |
2 |
120 |
2 |
2 |
379 |
2 |
qryTagScoreOrder
Code:
SELECT qryTagScore.UserID, qryTagScore.TagScore, (SELECT Count(*) FROM qryTagScore AS qryTagScore2 WHERE qryTagScore2.UserID = qryTagScore.UserID AND qryTagScore2.TagScore > qryTagScore.TagScore)+1 AS TagScoreOrder
FROM qryTagScore;
UserID |
TagScore |
TagScoreOrder |
1 |
3 |
1 |
1 |
3 |
1 |
1 |
2 |
3 |
2 |
3 |
1 |
2 |
2 |
2 |
2 |
2 |
2 |
What I would like the output to be is:
UserID |
TagScore |
TagScoreOrder |
1 |
3 |
1 |
1 |
3 |
2 |
1 |
2 |
3 |
2 |
3 |
1 |
2 |
2 |
2 |
2 |
2 |
3 |
Or to be able to take qryTagScoreOrder and select the first 10 rows per each UserID.
Any help?