using max in sql

[s4dadmin]

I am trying to get the correct sql. I am trying to get the image name for the image with the most hits. This concerns two fields: imageFileName and imageHits in tbl_Images. It is a subquery. I am receiving this error. Can anyone help me?

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
/512ryders/gallery/gallery.asp, line 48

sql = "Select eid, eventTitle, (Select Count(tbl_Images.imageID) From tbl_Images Where tbl_Events.eid = tbl_Images.imageEventID) As lngPhotoCount, (Select imageFileName From tbl_Images Where Exists (Select MAX(imageHits) from tbl_Images Where tbl_Events.eid = tbl_Images.imageEventID)) As strTopImage From tbl_Events Where eventHaveGallery =1 ORDER BY eventStartDate DESC;"
Set rsGallery = Server.CreateObject("ADODB.Recordset")
rsGallery.Open sql, adoCon

The particular code I am having trouble with is

(Select imageFileName From tbl_Images Where Exists (Select MAX(imageHits) from tbl_Images Where tbl_Events.eid = tbl_Images.imageEventID)) As strTopImage

Quincy

http://www.512ryders.com

 

[vongrunt]

Exists() doesn't make sense here... as long as subquery may return >1 value it will be problematic. Use TOP clause instead:

( Select top 1 imageFileName From tbl_Images where tbl_Events.eid=tbl_Images.imageEventID order by imageHits desc ) As strTopImage