CREATE TABLE marks_table (
ID INT PRIMARY KEY,
STUD_ID INT NOT NULL,
MARKS INT NOT NULL,
VERSION INT NOT NULL,
VERIFICATION_ID INT NOT NULL
);
-- 2) Insert sample data
INSERT INTO marks_table (ID, STUD_ID, MARKS, VERSION, VERIFICATION_ID) VALUES
( 1, 50, 90, 1, 2),
( 2, 22, 50, 1, 2),
( 3, 33, 20, 1, 2),
( 4, 10, 30, 1, 2),
( 5, 55, 50, 1, 2),
( 6, 55, 40, 2, 2),
( 7, 20, 60, 1, 2),
( 8, 30, 90, 1, 2),
( 9, 10, 88, 1, 3),
(10, 10, 45, 2, 3);
SELECT m.*
FROM marks_table m
JOIN (
SELECT
stud_id,
MAX(version) max_version
FROM marks_table
WHERE verification_id = 2
GROUP BY stud_id
) latest ON latest.stud_id = m.stud_id AND latest.max_version = m.version
WHERE m.verification_id = 2;
select * from marks_table a
where version = (select max(version) from
marks_table where stud_id = a.stud_id);
Telegram: DB developers channel