Mysql Question posted in
The official MySQL documentation can be found here.

MySQL SQL EXISTS clause fails as workaround for LIMIT usage restrictions

I was testing the query below thinking it would get me around the "can’t use LIMIT in subqueries" restriction. The query runs but the average is simply the average of all the records WHERE MScores.MemberId=1193 AND MScores.Div="FSO"
It seems to completely ignore the EXISTS clause.

The query should return the rolling average of the 5 (or less) most recent scores for a specific member in a specific division.

Any ideas why the EXISTS clause fails to affect the results?

  SELECT MScores.MemberId, MScores.`Div`, AVG(MScores.Final) AS AvgOfFinal
    FROM qryMatchScores MScores
    WHERE MScores.MemberId=1193 AND MScores.`Div`="FSO" AND 
    EXISTS (
        SELECT MatchDate 
        FROM qryMatchScores MDates
        WHERE 
            MDates.MemberId = MScores.MemberId AND 
            MDates.`Div` = MScores.`Div` AND
            MDates.MatchDate = MScores.MatchDate
        ORDER BY MatchDate DESC
        LIMIT 5
    );
Tags:

3

Answers


  1. 0

    You are trying to do two separate and incompatible things in your exists subquery: select the most recent 5 dates, and filter by the date of the outer row. You can’t have both. The closest thing to your approach I can come up with is:

    select AVG(MScores.Final) AvgOfFinal
from qryMatchScores MScores
where MScores.MemberId=1193 and MScores.`Div`="FSO" and 5 > (
  select count(distinct MDates.MatchDate)
  from qryMatchScores MDates
  where (MScores.MemberId,MScores.`Div`)=(MDates.MemberId,MDates.`Div`) and
      MDates.MatchDate > MScores.MatchDate
)
    Login or Signup to reply.
  2. 0

    Without some sample data and coresponding expected result, It’s hard to tell what you want to do. Another thing not completely explained is your statement:

    The query should return the rolling average of the 5 (or less) most recent
    scores for a specific member in a specific division

    As far as I understood it – this is something that could be solved using Avg() Over() analytic function with windowing clause and Row_Number() to handle ordering (and scope if needed) …

    Create Table 
  MatchScores ( MemberId Int, Division Text, Final Int, MatchDate Date );
--
--    S a m p l e    D a t a :
Insert Into MatchScores ( MemberId, Division, Final, MatchDate ) 
  VALUES
    ( 101, 'FSO', 100, '2024-07-01' ),
    ( 101, 'FSO', 110, '2024-07-02' ),
    ( 101, 'FSO', 120, '2024-07-03' ),
    ( 101, 'FSO', 120, '2024-07-04' ),
    ( 101, 'FSO', 100, '2024-07-05' ),
    ( 101, 'FSO', 130, '2024-07-06' ),
    ( 101, 'FSO', 100, '2024-07-07' ),
      --
    ( 201, 'FSO', 10, '2024-07-01' ),
    ( 201, 'FSO', 16, '2024-07-02' ),
    ( 201, 'FSO', 10, '2024-07-03' ),
    ( 201, 'FSO', 14, '2024-07-04' ),
    ( 201, 'FSO', 12, '2024-07-05' ),
      --
    ( 301, 'FSO', 1, '2024-07-01' ),
    ( 301, 'FSO', 3, '2024-07-02' );

    … SQL getting rolling (running) average in both ways ordered by date and an average of all rows fetched …

    SELECT    ms.MemberId, ms.Division, ms.Final, ms.MatchDate,  
          Avg(Final) Over( Partition By ms.MemberId 
                           Order By ms.RN 
                           Rows Between 4 Preceding And Current Row) as rolling_avg,
         Avg(Final) Over( Partition By ms.MemberId ) as avg, 
         Avg(Final) Over( Partition By ms.MemberId 
                           Order By ms.RN Desc
                           Rows Between 4 Preceding And Current Row) as reversed_rolling_avg,
        ms.RN
FROM    ( Select    MemberId, Division, Final, MatchDate, 
                    Row_Number() 
                         Over( Partition By MemberId Order By MatchDate Desc) as RN
          From      MatchScores
        ) ms
ORDER BY  ms.MemberId, ms.RN;

    /*      R e s u l t : 
MemberId  Division       Final  MatchDate   rolling_avg         avg  reversed_rolling_avg  RN
--------  --------  ----------  ----------  -----------  ----------  --------------------  --
     101  FSO              100  2024-07-07     100.0000    111.4286              114.0000   1
     101  FSO              130  2024-07-06     115.0000    111.4286              116.0000   2
     101  FSO              100  2024-07-05     110.0000    111.4286              110.0000   3
     101  FSO              120  2024-07-04     112.5000    111.4286              112.5000   4
     101  FSO              120  2024-07-03     114.0000    111.4286              110.0000   5
     101  FSO              110  2024-07-02     116.0000    111.4286              105.0000   6
     101  FSO              100  2024-07-01     110.0000    111.4286              100.0000   7
     201  FSO               12  2024-07-05      12.0000     12.4000               12.4000   1
     201  FSO               14  2024-07-04      13.0000     12.4000               12.5000   2
     201  FSO               10  2024-07-03      12.0000     12.4000               12.0000   3
     201  FSO               16  2024-07-02      13.0000     12.4000               13.0000   4
     201  FSO               10  2024-07-01      12.4000     12.4000               10.0000   5
     301  FSO                3  2024-07-02       3.0000      2.0000                2.0000   1
     301  FSO                1  2024-07-01       2.0000      2.0000                1.0000   2    */

    … if you want to filter (limit) the whole thing to just 5 latest rows per id – just add WHERE clause to outer query …

    SELECT    ms.MemberId, ms.Division, ms.Final, ms.MatchDate,  
          Avg(Final) Over( Partition By ms.MemberId 
                           Order By ms.RN 
                           Rows Between 4 Preceding And Current Row) as rolling_avg,
         Avg(Final) Over( Partition By ms.MemberId ) as avg, 
         Avg(Final) Over( Partition By ms.MemberId 
                           Order By ms.RN Desc
                           Rows Between 4 Preceding And Current Row) as reversed_rolling_avg,
       
        ms.RN
FROM    ( Select    MemberId, Division, Final, MatchDate, 
                    Row_Number() 
                         Over( Partition By MemberId Order By MatchDate Desc) as RN
          From      MatchScores
        ) ms
WHERE     ms.RN <= 5         -- filtering to just 5 latest rows per id
ORDER BY  ms.MemberId, ms.RN;

    /*      R e s u l t :
MemberId  Division       Final  MatchDate   rolling_avg         avg  reversed_rolling_avg  RN
--------  --------  ----------  ----------  -----------  ----------  --------------------  --
     101  FSO              100  2024-07-07     100.0000    114.0000              114.0000   1
     101  FSO              130  2024-07-06     115.0000    114.0000              117.5000   2
     101  FSO              100  2024-07-05     110.0000    114.0000              113.3333   3
     101  FSO              120  2024-07-04     112.5000    114.0000              120.0000   4
     101  FSO              120  2024-07-03     114.0000    114.0000              120.0000   5
     201  FSO               12  2024-07-05      12.0000     12.4000               12.4000   1
     201  FSO               14  2024-07-04      13.0000     12.4000               12.5000   2
     201  FSO               10  2024-07-03      12.0000     12.4000               12.0000   3
     201  FSO               16  2024-07-02      13.0000     12.4000               13.0000   4
     201  FSO               10  2024-07-01      12.4000     12.4000               10.0000   5
     301  FSO                3  2024-07-02       3.0000      2.0000                2.0000   1
     301  FSO                1  2024-07-01       2.0000      2.0000                1.0000   2    */

    See the fiddle here.

    NOTE:
    Depending on your actual data, you’ll need to add Division column to Partition By clause (with MemberId) of analytic functions (in case that 1 MemberId could be with 2 Divisions) OR to do the Division filtering in WHERE clause (if 1 MemberId is always with 1 Division) …

    Login or Signup to reply.
  3. 0

    It would help if you tried row_number()

    Select MemberId, `Div`, AVG(Final) as AvgOfFinal from 
(Select 
MScores.MemberId, 
MScores.`Div`, 
MScores.Final,
ROW_NUMBER() OVER (PARTITION BY MScores.MemberId, MScores.`Div` order by MScores.MatchDate desc) AS rn
from qryMatchScores MScores
where MScores.MemberId = 1193 AND MScores.`Div` = 'FSO'
) as RankedScores
where rn <= 5
group by 1,2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search