0

The following SQL returns the entire school attendance history (amongst a few other things) for a student in a database:

SELECT
  tblPUPIL.PUP_ID
  ,tblPUPIL.PER_ID
  ,tblPUPIL_ATTEND.tblPUPIL
  ,tblPUPIL_ATTEND.tblSCHOOL
  ,tblSCHOOL.SCH_ID
  ,tblSCHOOL.SCH_NAME
  ,tblPUPIL_ATTEND.PAT_FROM_DATE
  ,tblPUPIL_ATTEND.PAT_TO_DATE
  ,tblPUPIL_ATTEND.PAT_MAIN
FROM
  tblPUPIL_ATTEND
  INNER JOIN tblPUPIL
    ON tblPUPIL_ATTEND.tblPUPIL = tblPUPIL.PER_ID
  INNER JOIN tblSCHOOL
    ON tblPUPIL_ATTEND.tblSCHOOL = tblSCHOOL.SCH_ID
WHERE tblPUPIL.PER_ID = "PER1"

I need to return only the most recent school and corresponding columns, i.e. the MAX from date (tblPUPIL_ATTEND.PAT_FROM_DATE).

School History Output

Can someone please advise how on how I would write the SQL? I have a general idea from research on how to do it but cannot seem to get it to work.

Thom A
  • 88,727
  • 11
  • 45
  • 75
JamesL24
  • 1
  • 2
  • 2
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) Also, to go with your sample data, a clear explanation and expected results will help us help you. – Thom A May 18 '22 at 08:21
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A May 18 '22 at 08:21

1 Answers1

0
SELECT TOP 1
  tblPUPIL.PUP_ID
  ,tblPUPIL.PER_ID
  ,tblPUPIL_ATTEND.tblPUPIL
  ,tblPUPIL_ATTEND.tblSCHOOL
  ,tblSCHOOL.SCH_ID
  ,tblSCHOOL.SCH_NAME
  ,tblPUPIL_ATTEND.PAT_FROM_DATE
  ,tblPUPIL_ATTEND.PAT_TO_DATE
  ,tblPUPIL_ATTEND.PAT_MAIN
FROM
  tblPUPIL_ATTEND
  INNER JOIN tblPUPIL
    ON tblPUPIL_ATTEND.tblPUPIL = tblPUPIL.PER_ID
  INNER JOIN tblSCHOOL
    ON tblPUPIL_ATTEND.tblSCHOOL = tblSCHOOL.SCH_ID
WHERE tblPUPIL.PER_ID = "PER1"
ORDER BY tblPUPIL_ATTEND.PAT_FROM_DATE DESC

This will sort the table by descending tblPUPIL_ATTEND.PAT_FROM_DATE (most recent date first), and return the first result.

Myrto Pirli
  • 85
  • 8
  • Thank you, it does in this instance. My concern is that I won't be specifying the student ID in the final query - the student list will be generated in another, main dataset, and this query will be a second dataset linked to the main one via a lookup in Report Builder. I'm not sure how this would work if I needed the latest school for each child in a list? – JamesL24 May 18 '22 at 11:10
  • Perhaps a GROUP BY clause could be utilised in place of the WHERE clause to narrow down the results to just the most recent school per child ID? – JamesL24 May 18 '22 at 12:53