CODEWITHSUNDEEP
sqlsql-server
sharcfinz
sharcfinz

Reputation: 463

unique values using left outer join?

I have a list of ids in #IDS table, and I have a table containing duplicated data of the ids, and other information (id, title, state, approvedDate, etc.) needed in #ClassInfo.

Where am I going wrong here?

SELECT A.Id, A.ClassNum, A.Title, A.State, A.ApprovedDate, A.CreateDate, A.SubmittedDate
FROM #IDS B
LEFT OUTER JOIN #ClassInfo A
ON A.ClassNum = B.ClassId

I need to get the distinct values (of ClassNum, along with its related info) for the ids from the #IDS table

Upvotes: 0

Views: 84

Answers (1)

M.Ali
M.Ali

Reputation: 69564

;WITH CTE
AS
(
SELECT * , rn = ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)
FROM #ClassInfo
)
SELECT * 
FROM  #IDS S LEFT JOIN CTE CT
ON S.id = CT.id
WHERE rn = 1

Upvotes: 2

Related Questions