Choose a different Max ID for different customers

Question

situation:

we have monthly files that are uploaded to our data warehouse, but instead of replacing them with old loads, they simply compile on top of each other. files are uploaded in a few days.

so when we run the SQL script, we get duplicate records, to counteract this, we start the join through 10-20 clients and select Max (loadID), for example

SELECT Customer column 2 column 3 FROM MyTable WHERE LOADID = (SELECT MAX (LOADID) FROM MyTable WHERE Customer= 'ASDA') UNION SELECT Customer column 2 column 3 FROM MyTable WHERE LOADID = (SELECT MAX (LOADID) FROM MyTable WHERE Customer= 'TESCO' 

The above combining should be done for several clients, so I thought there should be a more efficient way.

we cannot use MAX (LoadID) in a SELECT statement, since a possible scenario could entail the following:

Monday: Asda, Tesco, Waitrose loaded in DW (with LoadID as 124)

Tuesday: Sainsburys loaded in DW (with LoadID as 125)

Wednesday: new Tesco loaded in DW (with LoadID as 126)

so I would like LoadID 124 Asda and Waitrose, 125 Sainsburys and 126 Tesco

Answer 1

Use window functions:

 SELECT t.* FROM (SELECT t.*, MAX(LOADID) OVER (PARTITION BY Customer) as maxLOADID FROM MyTable t ) t WHERE LOADID = maxLOADID; 

Answer 2

Will the subquery on the view fit your needs?

 select yourfields from yourtables join (select customer, max(loadID) maxLoadId from yourtables group by customer) derivedTable on derivedTable.customer = realTable.customer and loadId = maxLoadId