Eg. If we have a table like this exam_score (record_date refers to month when record is taken, Jan = 1, Feb = 2 etc):

Which SQL query allows me to generate a table where, for each student, we obtain the highest score obtained by fellow students in the same country before him?

So in this case, I should have something like

Currently my workaround is to use Python together with SQL queries to get what I want, but could we do this with SQL alone? I'm using MySQL, but maybe the database doesn't matter in terms of the SQL query.

CodePudding user response：

You can have SELECT in the column:

select s.*,
    (select max(score)
     from students
     where country = s.country
     and record_date < s.record_date) as max_score
from students s
order by record_date

CodePudding user response：

Schema (MySQL v8.0)

CREATE TABLE exam_score
    (`student` int, `country` varchar(2), `score` int, `record_date` int)
;
    
INSERT INTO exam_score
    (`student`, `country`, `score`, `record_date`)
VALUES
    (1, 'US', 70, 1),
    (2, 'US', 60, 2),
    (3, 'US', 80, 3),
    (4, 'IT', 60, 2),
    (5, 'IT', 100, 4),
    (6, 'BR', 80, 5)
;

Query #1

SELECT student
     , country
     , score
     , record_date
     , MAX(lag_score) OVER (PARTITION BY country ORDER BY record_date) AS max_score
FROM (
  SELECT student
       , country
       , record_date
       , score
       , LAG(score) OVER (PARTITION BY country ORDER BY record_date) AS lag_score
  FROM exam_score
  ) a
ORDER BY student;

Output:

View on DB Fiddle