I have a table with a number of columns (see attached). The key ones are SOPNUMBR which represents and invoice number and ITEMNUMBR which represents a product paid for in that invoice. I need to select a single ITEMNUMBR for each invoice. For 90% of the table this is fine because we usually have 1 ITEMNUMBR for each SOPNUMBR, but for some invoices there may be multiple rows. In the screenshot I have shown 1 SOPNUMBR that has 2 rows.
I don't care which ITEMNUMBR is selected, as long as I have 1 per SOPNUMBR and I don't want the SOPNUMBR to be repeated. I have no other column that would help such as a row count per SOPNUMBR.
Is there a way to limit the select to one row per SOPNUMBR and it can use whatever criteria SQL pleases to choose which row to keep? There are some date fields but they all have the same date so that is no help either.