[RESOLVED] Select Distinct by an Image Field

[vuyiswamb]

i have an interesting question for you guys.

SELECT   S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME],SP.PHOTO AS [PHOTO]
FROM SCHOOLPHOTO SP
INNER JOIN SCHOOL S
ON SP.SCHOOLID = SP.SCHOOLID
WHERE S.SCHOOLID  IN (SELECT SCHOOLID FROM #SCHOOLS)

the Photo Field is an Image Datatype Field, i now you can use them in a Distinct or group by , How can i go by have that field there without casting it , because if i cast it it will not work together in Silverlight or any other image control.

Thanks

[jmcilhinney]

That doesn't seem to make sense. You're retrieving the SchoolID so how could there be duplicates in the first place?

Regardless, if you want to compare binary data like that, I'd suggest generating a hash, e.g. MD5, and storing that in an additional column. You can then compare hashes rather than the actual binary data.

[vuyiswamb]

Thanks i found the Solution, this could do the Trick

 ;with cteSchools as (SELECT DISTINCT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME]
FROM SCHOOL S
 
WHERE S.SCHOOLID IN (SELECT SCHOOLID FROM #SCHOOLS))
 

SELECT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME],SP.PHOTO AS [PHOTO]
FROM cteSCHOOLS S
CROSS APPLY (Select TOP (1) PHOTO from SchoolPhoto SP
WHERE SP.SCHOOLID = S.SCHOOLID
ORDER BY SCHOOLID DESC) SP