select different Max ID's for different customer

Question

situation:

we have monthly files that get loaded into our data warehouse however instead of being replaced with old loads, these are just compiled on top of each other. the files are loaded in over a period of days.

so when running a SQL script, we would get duplicate records so to counteract this we run a union over 10-20 'customers' and selecting Max(loadID) e.g

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 union would have to be done for multiple customers so i was thinking surely there has to be a more efficient way.

we cant use a MAX (LoadID) in the SELECT statement as a possible scenario could entail the following;

Monday: Asda,Tesco,Waitrose loaded into 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 want LoadID 124 Asda & Waitrose, 125 Sainsburys, & 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;