如何查询列中具有特定值的最新记录

I have a DB of schools (there are only 3). In the DB, records are submitted regarding their performance.

I want an option for the user to query only the most recent entries for each school.

At the moment - I can query the most recent overall...

$result = mysqli_query($con,"SELECT * FROM reports, academicyears,schools,terms
WHERE reports.report_id=(SELECT MAX(report_id) FROM reports WHERE school_id=1 OR school_id=2 OR school_id=3)
AND academicyears.ay_id=reports.ay_id
AND schools.school_id=reports.school_id
AND terms.term_id=reports.term_id
ORDER BY reports.datesubmitted DESC

But I would like to be able to find the most recent for School 1 & School 2 & School 3 at the same time.

I can see the OR isn't giving me what I want... it just returns the latest record! What should the query be?

Also - the next step is then find the most recent records without having to name the school ids at all. As the number of schools grow - that would be totally inefficient! Is that possible?

Thanks for all help. One of the solutions offered suggested a UNION query. This resulted in a 'duplicate column' error. So, I renamed the columns within the database and updated the query language and it now works!

$result = mysqli_query($con,"SELECT * FROM (
    (
        SELECT *
        FROM reports, academicyears, schools, terms 
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE rep_school_id=1
        )
        AND academicyears.ay_id=reports.rep_ay_id
        AND schools.school_id=reports.rep_school_id
        AND terms.term_id=reports.rep_term_id
    ) UNION (
        SELECT *
        FROM reports, academicyears, schools, terms 
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE rep_school_id=2
        )
        AND academicyears.ay_id=reports.rep_ay_id
        AND schools.school_id=reports.rep_school_id
        AND terms.term_id=reports.rep_term_id
    ) UNION (
        SELECT *
        FROM reports, academicyears, schools, terms 
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE rep_school_id=3
        )
        AND academicyears.ay_id=reports.rep_ay_id
        AND schools.school_id=reports.rep_school_id
        AND terms.term_id=reports.rep_term_id
    )
) AS Latest
ORDER BY datesubmitted DESC
");?>

Thanks again for anyone who helped.

A query like this should do the trick. It's three separate queries, 1 for each school, put together by the UNION command:

FROM (
    (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=1
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    ) UNION (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=2
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    ) UNION (
        SELECT * 
        FROM reports, academicyears, schools, terms
        WHERE reports.report_id=(
            SELECT MAX(report_id) 
            FROM reports 
            WHERE school_id=3
        )
        AND academicyears.ay_id=reports.ay_id
        AND schools.school_id=reports.school_id
        AND terms.term_id=reports.term_id
    )
) AS Latest
ORDER BY datesubmitted DESC

Technically this is running 6 queries in 1, so it will not be terribly efficient. As the number of entries gets larger, this query will get slower.

Youre looking for window functions which aren't in mysql but can be emulated as follows:

SELECT
  *
FROM
  reports, academicyears,schools,terms   ,
  (
    SELECT 
      GROUP_CONCAT(top_codes_per_group) AS top_codes
    FROM
     (
       SELECT 
      SUBSTRING_INDEX(GROUP_CONCAT(report_id.   ORDER By datesubmitted DESC), ',', 5) AS top_codes_per_group
    FROM
      reports, academicyears,schools,terms       
      WHERE school_id=1 OR school_id=2 OR.   school_id=3)
AND academicyears.ay_id=reports.ay_id
AND schools.school_id=reports.school_id
AND terms.term_id=reports.term_id GROUP BY

  ) s_top_codes_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(Code, top_codes)
ORDER BY
 School,
 Datesubmitted DESC
;

This gives the top 5 reports per school.