2

I'm trying to follow the answer from this question.

However it's not working for me and I can't see why.

SELECT h.*
FROM `student-history` h
LEFT OUTER JOIN `student-history` h2 ON h.id = h2.id AND h.`school-year` < h2.`school-year`
WHERE 
h2.id IS NULL

My SQL is pretty much exactly the same as Adrian's in his answer yet its not producing the same results. Basically the student-history table has a tinyint(1) column called school-year Which is typically a number between 1-3. Each user could have multiple entries. So I want to find the highest year row.

I don't totally understand his answer so I'm not really able to proceed any further but if anyone can help me as to where I'm going wrong that would be great.

If I add to the WHERE clause in the above statement to narrow it down by user, such as AND h.userID = 54, I still get three rows with all three years the student was present. The LEFT OUTER JOIN doesnt seem to be making a difference at all.

I've created an SQL Fiddle here.

GMB
  • 216,147
  • 25
  • 84
  • 135
Chud37
  • 4,907
  • 13
  • 64
  • 116
  • Please put what is needed to ask in your question, not just at a link. That includes a [mre]. Please ask 1 specific question. Various problems to pin down here are faqs. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 13 '20 at 00:42
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Oct 13 '20 at 00:46

1 Answers1

2

You should be joining on the userID instead of the id. Apart from that, your query is fine.

SELECT h.*
FROM `student-history` h
LEFT OUTER JOIN `student-history` h2 ON h.userID = h2.userID AND h.`school-year` < h2.`school-year`
WHERE 
h2.id IS NULL
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Wow! Can you explain why that makes the difference? In the answer i referenced, they join the ID? – Chud37 Oct 03 '19 at 09:26
  • I've now seen in the original question that ID was not unique. So that makes sense now. Okay thank you for your help! – Chud37 Oct 03 '19 at 09:28
  • The idea of the `LEFT JOIN` is to check that there is no other record for the same student with a highest `school-year`... And students are identified by their `userID`. – GMB Oct 03 '19 at 09:29