Select Distinct by an Image Field

Vimalsoft Pty Ltd

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

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

David Mujica

How about generating a hash code for your image and store that value in your table along with the image. You could then use the hash code in your group by clause. check this article out ... http://www.vcskicks.com/image-hash.php[^] It was the first thing that popped up in my Google search. Good luck. :thumbsup:

Vimalsoft Pty Ltd

I need a Solution in T-SQL

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Pete OHanlon

What version of SQL Server are you using? If it's sufficiently advanced, you could apply this as a CLR Trigger.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Vimalsoft Pty Ltd

I am SQL 2008

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Pete OHanlon

Then it's job done. You can use a SQL CLR trigger and store the hash when the image is inserted or updated.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Vimalsoft Pty Ltd

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

Thanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

Lost User

Use the T-SQL function hashbytes like this:

select hashbytes('MD5', 'your value')