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.
You can do fancy things that will find the "first row" [based on some Sort Order for a given SOPNUMBR (usually using ROW_NUMBER OVER() ... ), but given your "don't care" spec I would just use either MIN or MAX.
SELECT SOPNUMBR, SomeOtherColumn, MIN(ITEMNUMBR) AS [MIN_ITEMNUMBR]
FROM MyTable
GROUP BY SOPNUMBR, SomeOtherColumn
ORDER BY ...
A sometimes-useful side effect is to output both MIN and MAX. That gives you 2x ITEMNUMBR per SOPNUMBR row output. Obviously only helpful for invoices that have only one or two products, but I've had situations where that was "good enough". You can also include COUNT(*) and if more than 2 then you know there were "more items present which have not been shown"
Finally, and also probably not relevant!!, you could output (as a single column) a comma delimited list of all the ITEMNUMBR codes for that particular SOPNUMBR
