Your requirement isn't totally clear Alan so I'll take a shot at interpretation and suggest ROW_NUMBER(), like this:
SELECT
rn = ROW_NUMBER() OVER (
PARTITION BY Software_Name_Raw, Software_Version_Raw, Software_Publisher_Raw
ORDER BY Load_Date),
Software_Name_Raw,
Software_Version_Raw,
Software_Publisher_Raw,
Software_Category,
Software_Subcategory,
MSDN_Flag,
CDL_Flag,
Source,
Pending_Classification_Flag,
Auto_Classification_Flag,
Software_Classification_Version,
Manual_Deletion,
Load_Date
FROM lookuptable
Run the code and examine the output. I'd guess you are interested in rows where rn=1.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden