Q:

SQL Inner join and Limit records

This is a simplification table structure that i have.
I have two tables.
TABLE 1

       ID        PTS        PID        TID
    ----------------------------------------
        1         3          1          22
        2         10         1          22
        3         5          1          22
        4         1          2          58
        5         0          2          58
        7         0          3          47
        8         5          3          47
        15        5          4          51
        15        3          4          51
        9         7          3          6
        10        0          1          8
        11        2          1          8
        12        4          5          1
        13        1          6          9
        14        2          5          12
        15        5          4          61
        15        6          4          61
        15        2          4          61
        16        0          7          100
        ect.      ect.       ect.       ect.

TABLE 2

      NAME       PID
    -------------------
      Jhon       1
      Peter      2
      Lisa       3
      Doe        4
      Joey       5
      Mike       6
      Debby      7
      ect.       ect.

Now i need to select the latest two PTS from each TID and match them whit PID row on table 2. and calculate the Average.
THE DESIRED OUTCOME

      NAME       AVG
    -------------------
      Jhon       4,25
      Peter      1,00
      Lisa       6,00
      Doe        4,00
      Joey       3,00
      Mike       1,00
      Debby      0,00
      ect.       ect.

Clarification: the PID row are relational. Multiple TIDs can have the same PID and TIDs can have multiple PTS. I am using PDO socket.
At the time my query is:
$query = $db->prepare("SELECT IFNULL(AVG(pts), 0) AS P, TA1.PID AS TA1PID, name AS N FROM ".
                    "table1 TA1 LEFT JOIN table2 TA2 ON TA1.PID = TA2.PID ".
                    "GROUP BY name, TA2.PID ".
                    "ORDER BY TA1.id DESC");

But this calculates all the points(PTS) from TID. but i would like to only calculate the Two latest points(PTS) per TID. I have tried different queries the whole day but i cant figure it out. I am pretty new to SQL I managed to get one example working but with pure PHP and its not pretty :D
sqlFiddle: LINK
The problem is that it calculates all the TID points in Average. It only should calculate the two last entries per TID
I hope this is a clear question. I have tried my best to explain my problem. If any questions please ask. I have read other similar problems than mine here on Stackoverflow but i could not modify them to work for me.

A:

You can select only the most recent 2 rows from the first table using this query
select t1.id, t1.pts, t1.pid, t1.tid
from table1 t1
join table1 t2 on t2.id >= t1.id and t1.tid = t2.tid
group by t1.id
having count(*) <= 2

and plug it into your original query instead of table 1
SELECT IFNULL(AVG(pts), 0) AS AVG, TA1.PID AS 
Table1_ID, name AS Name FROM
(
    select t1.id, t1.pts, t1.pid, t1.tid
    from table1 t1
    join table1 t2 on t2.id >= t1.id and t1.tid = t2.tid
    group by t1.id
    having count(*) <= 2
)
TA1 LEFT JOIN table2 TA2 ON TA1.PID = TA2.PID
      GROUP BY name, TA2.PID
      ORDER BY TA1.id DESC

