Can someone help me with a T-SQL query I am trying to create. I am sure it will all look very simple when it is resolved, but after a couple of hours on my own I still have not cracked it.
Simplified, I have a table with say 4 fields, Code, Name1, Name2, Name3
The same "Code" value might appear in several records because it may have different "Names" associated with it. This means the table might have 20,000 different records, but there are perhaps only 10,000 distinct values for "Code".
(Not my data - it comes from Amazon.)
I would like to list each "unique" value of "Code", and am happy to see the "Name" fields from the first (any) record for that "distinct" value of "Code".
I have tried things like the following. I know this fails (and I am combining WHERE with ON) but it hopefully gives an idea of what I am trying to achieve :
SELECT AMZ1.Code AS 'CODE', AMZ2.*
FROM VIEW_AmazonDataGroupedByCode AS AMZ1
LEFT OUTER JOIN (SELECT TOP 1 *
FROM TABLE_AmazonData AS AMZ3
WHERE AMZ3.Code = AMZ1.Code) AS AMZ2 ON AMZ2.Code = AMZ1.Code
Can anyone suggest how I read the additional fields from the first (any) record containing a distinct "Code"?