By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data_meaareport (
personid INT NULL,
leftresult INT NULL,
rightresult INT NULL,
bestear INT NULL,
recordid INT NOT NULL
);
INSERT INTO data_meaareport VALUES
(33122,1,1,0,2), (33122,6,7,0,1),
(41228,1,1,0,3), (41228,1,8,0,2),
(41228,6,7,0,1), (45336,1,1,0,3),
(45336,Null,8,1,2), (45336,1,1,0,1),
(54122,NULL,2,1,2), (54122,6,6,0,1),
(371339,2,2,0,4), (371339,NULL,2,1,3),
(371339,3,3,0,2), (371339,3,3,0,1);
SELECT *
FROM data_meaareport
order by personid, recordid desc;
Records: 14 Duplicates: 0 Warnings: 0
| personid | leftresult | rightresult | bestear | recordid |
|---|---|---|---|---|
| 33122 | 1 | 1 | 0 | 2 |
| 33122 | 6 | 7 | 0 | 1 |
| 41228 | 1 | 1 | 0 | 3 |
| 41228 | 1 | 8 | 0 | 2 |
| 41228 | 6 | 7 | 0 | 1 |
| 45336 | 1 | 1 | 0 | 3 |
| 45336 | null | 8 | 1 | 2 |
| 45336 | 1 | 1 | 0 | 1 |
| 54122 | null | 2 | 1 | 2 |
| 54122 | 6 | 6 | 0 | 1 |
| 371339 | 2 | 2 | 0 | 4 |
| 371339 | null | 2 | 1 | 3 |
| 371339 | 3 | 3 | 0 | 2 |
| 371339 | 3 | 3 | 0 | 1 |
WITH cte AS (
SELECT personid, MAX(recordid) mxr
FROM data_meaareport
GROUP BY personid)
SELECT mlr.personid, mxr,
CASE WHEN rightresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND rightresult IS NULL THEN 0
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 THEN rightresult
ELSE rightresult
END rr
FROM data_meaareport mlr
JOIN cte
ON mlr.personid=cte.personid
| personid | mxr | rr |
|---|---|---|
| 33122 | 2 | 999 |
| 33122 | 2 | 7 |
| 41228 | 3 | 999 |
| 41228 | 3 | 8 |
| 41228 | 3 | 7 |
| 45336 | 3 | 999 |
| 45336 | 3 | 8 |
| 45336 | 3 | 999 |
| 54122 | 2 | 999 |
| 54122 | 2 | 6 |
| 371339 | 4 | 999 |
| 371339 | 4 | 999 |
| 371339 | 4 | 3 |
| 371339 | 4 | 3 |
WITH cte AS (
SELECT personid, MAX(recordid) mxr
FROM data_meaareport
GROUP BY personid),
cte2 AS (
SELECT mlr.personid, mxr,
GROUP_CONCAT(IFNULL(leftresult,'NULL')
ORDER BY CASE WHEN leftresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND leftresult IS NULL THEN 0
ELSE leftresult
END) lr,
GROUP_CONCAT(IFNULL(rightresult,'NULL')
ORDER BY CASE WHEN rightresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND rightresult IS NULL THEN 0
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 THEN 1
ELSE 2
END) rr
FROM data_meaareport mlr
JOIN cte
ON mlr.personid=cte.personid
GROUP BY mlr.personid, mxr)
SELECT *,
SUBSTRING_INDEX(lr,',',1) AS fleftresult,
SUBSTRING_INDEX(rr,',',1) AS frightresult
FROM cte2;
| personid | mxr | lr | rr | fleftresult | frightresult |
|---|---|---|---|---|---|
| 33122 | 2 | 6,1 | 7,1 | 6 | 7 |
| 41228 | 3 | 6,1,1 | 8,7,1 | 6 | 8 |
| 45336 | 3 | NULL,1,1 | 8,1,1 | NULL | 8 |
| 54122 | 2 | NULL,6 | 6,2 | NULL | 6 |
| 371339 | 4 | NULL,3,3,2 | 3,3,2,2 | NULL | 3 |
WITH cte AS (
SELECT personid, MAX(recordid) mxr
FROM data_meaareport
GROUP BY personid),
cte2 AS (
SELECT mlr.personid, mxr,
GROUP_CONCAT(IFNULL(leftresult,'NULL')
ORDER BY CASE WHEN leftresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND leftresult IS NULL THEN 0
ELSE leftresult
END) lr,
GROUP_CONCAT(IFNULL(rightresult,'NULL')
ORDER BY CASE WHEN rightresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND rightresult IS NULL THEN 0
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 THEN 1
ELSE 2
END) rr
FROM data_meaareport mlr
JOIN cte
ON mlr.personid=cte.personid
GROUP BY mlr.personid, mxr)
SELECT personid,
SUBSTRING_INDEX(lr,',',1) AS fleftresult,
SUBSTRING_INDEX(rr,',',1) AS frightresult,
mxr AS mrecordid
FROM cte2;
| personid | fleftresult | frightresult | mrecordid |
|---|---|---|---|
| 33122 | 6 | 7 | 2 |
| 41228 | 6 | 8 | 3 |
| 45336 | NULL | 8 | 3 |
| 54122 | NULL | 6 | 2 |
| 371339 | NULL | 3 | 4 |
WITH cte AS (
SELECT personid pid, MAX(recordid) mxr
FROM data_meaareport
GROUP BY personid)
SELECT *,
LEAD(leftresult) OVER
(PARTITION BY personid
ORDER BY IFNULL(leftresult,0),
CASE WHEN leftresult IN (1,2,12) THEN 999 END) AS lr,
LAG(rightresult) OVER (PARTITION BY personid
ORDER BY CASE WHEN rightresult IN (1,2,12) THEN 999
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 AND rightresult IS NULL THEN 0
WHEN mlr.recordid BETWEEN mxr-1 AND mxr+1 THEN 1
ELSE 2
END) AS rr,
MAX(recordid) OVER (PARTITION BY personid) AS ltrcd
FROM data_meaareport mlr
JOIN cte
ON mlr.personid=cte.pid
ORDER BY personid, recordid DESC
| personid | leftresult | rightresult | bestear | recordid | pid | mxr | lr | rr | ltrcd |
|---|---|---|---|---|---|---|---|---|---|
| 33122 | 1 | 1 | 0 | 2 | 33122 | 2 | 6 | 7 | 2 |
| 33122 | 6 | 7 | 0 | 1 | 33122 | 2 | null | null | 2 |
| 41228 | 1 | 1 | 0 | 3 | 41228 | 3 | 1 | 7 | 3 |
| 41228 | 1 | 8 | 0 | 2 | 41228 | 3 | 6 | null | 3 |
| 41228 | 6 | 7 | 0 | 1 | 41228 | 3 | null | 8 | 3 |
| 45336 | 1 | 1 | 0 | 3 | 45336 | 3 | 1 | 8 | 3 |
| 45336 | null | 8 | 1 | 2 | 45336 | 3 | 1 | null | 3 |
| 45336 | 1 | 1 | 0 | 1 | 45336 | 3 | null | 1 | 3 |
| 54122 | null | 2 | 1 | 2 | 54122 | 2 | 6 | 6 | 2 |
| 54122 | 6 | 6 | 0 | 1 | 54122 | 2 | null | null | 2 |
| 371339 | 2 | 2 | 0 | 4 | 371339 | 4 | 3 | 2 | 4 |
| 371339 | null | 2 | 1 | 3 | 371339 | 4 | 2 | 3 | 4 |
| 371339 | 3 | 3 | 0 | 2 | 371339 | 4 | 3 | null | 4 |
| 371339 | 3 | 3 | 0 | 1 | 371339 | 4 | null | 3 | 4 |
WITH cte AS
(SELECT *,
LEAD(leftresult) OVER (PARTITION BY personid ORDER BY IFNULL(leftresult,0)) AS lr,
LAG(rightresult) OVER (PARTITION BY personid) AS rr,
MAX(recordid) OVER (PARTITION BY personid) AS ltrcd
FROM data_meaareport mlr)
SELECT *,
(SELECT lr FROM cte c1 WHERE cte.personid=c1.personid AND c1.lr NOT IN (1,2,12))
FROM cte
WITH cte AS
(SELECT *,
LAG(leftresult) OVER (PARTITION BY personid
ORDER BY CASE WHEN leftresult NOT IN (1,2,12) THEN 1
WHEN leftresult IS NULL THEN 0 ELSE 999 END) AS lr,
LAG(rightresult) OVER (PARTITION BY personid
ORDER BY CASE WHEN rightresult NOT IN (1,2,12) THEN 1
WHEN rightresult IS NULL THEN 0 ELSE 999 END) AS rr,
ROW_NUMBER() OVER
(PARTITION BY personid ORDER BY personid, recordid DESC) AS rn
FROM data_meaareport mlr
ORDER BY personid, recordid DESC)
SELECT *
FROM cte
WHERE rn=1
| personid | leftresult | rightresult | bestear | recordid | lr | rr | rn |
|---|---|---|---|---|---|---|---|
| 33122 | 1 | 1 | 0 | 2 | 6 | 7 | 1 |
| 41228 | 1 | 1 | 0 | 3 | 6 | 8 | 1 |
| 45336 | 1 | 1 | 0 | 3 | null | 8 | 1 |
| 54122 | null | 2 | 1 | 2 | null | 6 | 1 |
| 371339 | 2 | 2 | 0 | 4 | 3 | 2 | 1 |
WITH cte AS
(SELECT *,
LAG(leftresult) OVER (PARTITION BY personid
ORDER BY CASE WHEN leftresult NOT IN (1,2,12) THEN leftresult
WHEN leftresult IS NULL THEN 0 ELSE 999 END) AS lr,
LAG(rightresult) OVER (PARTITION BY personid
ORDER BY CASE WHEN rightresult NOT IN (1,2,12) THEN rightresult
WHEN rightresult IS NULL THEN 0 ELSE 999 END) AS rr,
ROW_NUMBER() OVER
(PARTITION BY personid ORDER BY personid, recordid DESC) AS rn
FROM data_meaareport mlr
ORDER BY personid, recordid DESC)
SELECT *
FROM data_meaareport mr
LEFT JOIN cte
ON mr.personid=cte.personid
AND rn=1
| personid | leftresult | rightresult | bestear | recordid | personid | leftresult | rightresult | bestear | recordid | lr | rr | rn |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 33122 | 1 | 1 | 0 | 2 | 33122 | 1 | 1 | 0 | 2 | 6 | 7 | 1 |
| 33122 | 6 | 7 | 0 | 1 | 33122 | 1 | 1 | 0 | 2 | 6 | 7 | 1 |
| 41228 | 1 | 1 | 0 | 3 | 41228 | 1 | 1 | 0 | 3 | 6 | 8 | 1 |
| 41228 | 1 | 8 | 0 | 2 | 41228 | 1 | 1 | 0 | 3 | 6 | 8 | 1 |
| 41228 | 6 | 7 | 0 | 1 | 41228 | 1 | 1 | 0 | 3 | 6 | 8 | 1 |
| 45336 | 1 | 1 | 0 | 3 | 45336 | 1 | 1 | 0 | 3 | null | 8 | 1 |
| 45336 | null | 8 | 1 | 2 | 45336 | 1 | 1 | 0 | 3 | null | 8 | 1 |
| 45336 | 1 | 1 | 0 | 1 | 45336 | 1 | 1 | 0 | 3 | null | 8 | 1 |
| 54122 | null | 2 | 1 | 2 | 54122 | null | 2 | 1 | 2 | null | 6 | 1 |
| 54122 | 6 | 6 | 0 | 1 | 54122 | null | 2 | 1 | 2 | null | 6 | 1 |
| 371339 | 2 | 2 | 0 | 4 | 371339 | 2 | 2 | 0 | 4 | 3 | 2 | 1 |
| 371339 | null | 2 | 1 | 3 | 371339 | 2 | 2 | 0 | 4 | 3 | 2 | 1 |
| 371339 | 3 | 3 | 0 | 2 | 371339 | 2 | 2 | 0 | 4 | 3 | 2 | 1 |
| 371339 | 3 | 3 | 0 | 1 | 371339 | 2 | 2 | 0 | 4 | 3 | 2 | 1 |
select m1.personid,m1.leftresult,m1.rightresult,m1.bestear,m1.ascrnk,
case when m1.leftresult in (1,2,12) then
(select m2.leftresult from MR m2 where m1.personid = m2.personid and m2.ascrnk = m1.ascrnk -1) end leftlagg
from MR m1
order by m1.personid asc, m1.ascrnk desc
Table 'fiddle.MR' doesn't exist