Select field corresponding to min sequence number

Question

I have two tables to query. The first contains a log of complaints, the second contains note records for each complaint, where there can be multiple note records for each complaint with the first line in the note being assigned a sequence value of 1, the second a sequence value of 2, etc. Sometimes there will be no note at all.

What is the best way to set up the below query so that I select the note corresponding to sequence 1 for each complaint?

SELECT table1.complaint_id, table1.itemnumber, table1.quantity, ???table2.FIRST_NOTE_IN_SEQUENCE??? FROM table1 LEFT JOIN table2 ON table1.complaint_id = table2.complaint_id

As a bonus, anybody know a way to set this query up to show all note records for each complaint without causing repetitions of complaint id, item number and quantity?

question from:https://stackoverflow.com/questions/65902736/select-field-corresponding-to-min-sequence-number

Answer 1

Waitting for answers