Results 1 to 3 of 3
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181

    OVER PARTITION type query, ranking, select top 10

    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?

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    you need a field to indicate order - in your final output - what differentiates row 1 from row 2?

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    sorry that should read

    you need a field to indicate unique order

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2015, 02:54 PM
  2. Partition with Access SQL
    By b82726272 in forum Queries
    Replies: 1
    Last Post: 04-08-2014, 03:59 AM
  3. Replicating ROW_NUMBER OVER (PARTITION BY...)
    By normanj in forum Queries
    Replies: 2
    Last Post: 11-27-2012, 07:36 AM
  4. Provide Ranking & Select on the basis of Rank
    By dolovenature in forum Programming
    Replies: 3
    Last Post: 09-11-2012, 04:46 AM
  5. Ranking (Look for previous ranking)
    By leobear in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 05:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums