Select Distinct by an Image Field [Resolved]

Posted by Vuyiswamb under Sql Server on 5/31/2011 | Points: 10 | Views : 2698 | Status : [Member] [MVP] [Administrator] | Replies : 1
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

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Vuyiswamb on: 5/31/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down

Resolved
this did the Job

 ;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


Thank you for posting at Dotnetfunda
[Administrator]

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response