SQL Inner join and limit records
This is the simplification table structure 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 last two PTS from each TID and match them to the PID row in 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 string is relational. Multiple TIDs can have the same PID and TIDs can have multiple PTSs. I am using PDO socket.
At the time of my request:
$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 points (PTS) from TID. but I would like to calculate only the last two points (PTS) on the TID. I've tried different queries all day long, but I can't figure it out. I am new to SQL. I managed to get one example working, but with pure PHP and its not very pretty: D
sqlFiddle: LINK
The problem is that it calculates all TID points on average. It should only compute the last two records per TID
I hope this is a clear question. I tried my best to explain my problem. If you have any questions, please ask. I read other similar problems than mine here on Stackoverflow, but I couldn't modify them to work for me.
source to share
You can only select the last 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 paste 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
source to share
The Per N group is pretty straightforward in most databases. Just use ROW_NUMBER. Unfortunately MySQL doesn't have them, so we have to simulate it.
SELECT name,
Avg(PTS)
FROM
(
SELECT *,
CASE
WHEN @previousPID IS NULL
OR @previousTID IS NULL
OR ( @previousPID = ORDERED.pid
AND @previousTID = ORDERED.tid )
THEN @rownum := @rownum + 1
ELSE @rownum := 1
end rn,
@previousPID := ORDERED.pid ,
@previousTID := ORDERED.tid
FROM (SELECT t2.name,
t2.pid,
t1.id,
t1.tid ,
t1.Pts
FROM table2 t2
INNER JOIN table1 t1
ON T2.pid = t1.pid
ORDER BY t1.pid,
t1.tid,
t1.id DESC)ORDERED,
(SELECT @rownum := 0,
@previousPID := NULL,
@previousTID := NULL) t) CTE
WHERE CTE.rn <= 2
GROUP BY name
Which has the following Results
| NAME | AVG(PTS) |
|-----------|----------|
| Doe | 4 |
| Jhon | 4.25 |
| Joey | 3 |
| Lisa | 4 |
| Mike | 1 |
| No points | 0 |
| Peter | 0.5 |
Looking at the intermediate results can help you understand how CASE statements generate rowan trees
| NAME | PID | ID | TID | PTS | @ROWNUM := 0 | @PREVIOUSPID := NULL | @PREVIOUSTID := NULL | RN | @PREVIOUSPID := ORDERED.PID | @PREVIOUSTID := ORDERED.TID |
|-----------|-----|----|-----|-----|--------------|----------------------|----------------------|----|-----------------------------|-----------------------------|
| Jhon | 1 | 3 | 22 | 5 | 0 | (null) | (null) | 1 | 1 | 22 |
| Jhon | 1 | 2 | 22 | 10 | 0 | (null) | (null) | 2 | 1 | 22 |
| Jhon | 1 | 1 | 22 | 3 | 0 | (null) | (null) | 3 | 1 | 22 |
| Jhon | 1 | 12 | 8 | 2 | 0 | (null) | (null) | 1 | 1 | 8 |
| Jhon | 1 | 11 | 8 | 0 | 0 | (null) | (null) | 2 | 1 | 8 |
| Peter | 2 | 5 | 58 | 0 | 0 | (null) | (null) | 1 | 2 | 58 |
| Peter | 2 | 4 | 58 | 1 | 0 | (null) | (null) | 2 | 2 | 58 |
| Lisa | 3 | 7 | 47 | 5 | 0 | (null) | (null) | 1 | 3 | 47 |
| Lisa | 3 | 6 | 47 | 0 | 0 | (null) | (null) | 2 | 3 | 47 |
| Lisa | 3 | 10 | 6 | 7 | 0 | (null) | (null) | 1 | 3 | 6 |
| Doe | 4 | 9 | 51 | 3 | 0 | (null) | (null) | 1 | 4 | 51 |
| Doe | 4 | 8 | 51 | 5 | 0 | (null) | (null) | 2 | 4 | 51 |
| Doe | 4 | 19 | 61 | 2 | 0 | (null) | (null) | 1 | 4 | 61 |
| Doe | 4 | 17 | 61 | 6 | 0 | (null) | (null) | 2 | 4 | 61 |
| Doe | 4 | 16 | 61 | 5 | 0 | (null) | (null) | 3 | 4 | 61 |
| Joey | 5 | 13 | 1 | 4 | 0 | (null) | (null) | 1 | 5 | 1 |
| Joey | 5 | 15 | 12 | 2 | 0 | (null) | (null) | 1 | 5 | 12 |
| Mike | 6 | 14 | 9 | 1 | 0 | (null) | (null) | 1 | 6 | 9 |
| No points | 7 | 18 | 100 | 0 | 0 | (null) | (null) | 1 | 7 | 100 |
source to share