CODEWITHSUNDEEP
sqlms-access-2013
Teamgilla
Teamgilla

Reputation: 69

Access 2013 - Query not returning correct Number of Results

I am trying to get the query below to return the TWO lowest PlayedTo results for each PlayerID. 

select
    x1.PlayerID, x1.RoundID, x1.PlayedTo
from P_7to8Calcs as x1
where
    (
    select count(*)
    from P_7to8Calcs as x2
    where x2.PlayerID = x1.PlayerID
    and x2.PlayedTo <= x1.PlayedTo
    ) <3
order by PlayerID, PlayedTo, RoundID;

Unfortunately at the moment it doesn't return a result when there is a tie for one of the lowest scores. A copy of the dataset and code is here http://sqlfiddle.com/#!3/4a9fc/13.

PlayerID 47 has only one result returned as there are two different RoundID's that are tied for the second lowest PlayedTo. For what I am trying to calculate it doesn't matter which of these two it returns as I just need to know what the number is but for reporting I ideally need to know the one with the newest date.

One other slight problem with the query is the time it takes to run. It takes about 2 minutes in Access to run through the 83 records but it will need to run on about 1000 records when the database is fully up and running.

Any help will be much appreciated.

Upvotes: 1

Views: 265

Answers (1)

halfbit
halfbit

Reputation: 3464

Resolve the tie by adding DatePlayed to your internal sorting (you wanted the one with the newest date anyway):

select
    x1.PlayerID, x1.RoundID
    , x1.PlayedTo
from P_7to8Calcs as x1
where
    (
    select count(*)
    from P_7to8Calcs as x2
    where x2.PlayerID = x1.PlayerID
    and (x2.PlayedTo < x1.PlayedTo
         or x2.PlayedTo = x1.PlayedTo
            and x2.DatePlayed >= x1.DatePlayed
         )
    ) <3
order by PlayerID, PlayedTo, RoundID;

For performance create an index supporting the join condition. Something like:

create index P_7to8Calcs__PlayerID_RoundID on P_7to8Calcs(PlayerId, PlayedTo);

Note: I used your SQLFiddle as I do not have Acess available here.

Edit: In case the index does not improve performance enough, you might want to try the following query using window functions (which avoids nested sub-query). It works in your SQLFiddle but I am not sure if this is supported by Access.

select x1.PlayerID, x1.RoundID, x1.PlayedTo
from (
  select PlayerID, RoundID, PlayedTo
  , RANK() OVER (PARTITION BY PlayerId ORDER BY PlayedTo, DatePlayed DESC) AS Rank
  from P_7to8Calcs
  ) as x1
where x1.RANK < 3
order by PlayerID, PlayedTo, RoundID;

See OVER clause and Ranking Functions for documentation.

Upvotes: 2

Related Questions