Posted by Erland Sommarskog on 11/30/06 22:53

Douglas (post.it@the.group.so.everyone.can.learn) writes:
> ok, I thought id try and strip it down to its bare essentials, but i
> perhaps should have mentioned...
>
> I am SELECTing * FROM (SELECT TOP @ImageQty FROM tblImages ORDER BY
> NEWID() ) AS [ImageTable] ORDER BY [ImageWidth];
>
> So i can ask for 5 images chosen at random and sorted by width.
>
> The trouble is, that some images are panoramic, and up to 9 times wider
> than standard pics. Other shots are portait, and therefore a lot
> narrower. This causes display anomolies in the resulting html :(

It's probably easiest to use a temp table/table variable for this:

DECLARE @tbl TABLE (imageid int NOT NULL PRIMARY KEY,
width int NOT NULL,
rowno int NOT NULL,
accum_width int NULL)

INSERT @tbl (imageid, width, rowno)
SELECT TOP (@ImageQty)
imageid, width, row_number(ORDER BY width, newid())
FROM tblImages
ORDER BY newid()

UPDATE @tbl
SET accum_width = (SELECT SUM(b.width)
FROM @tbl b
WHERE b.rowno <= a.rowno)
FROM @tbl a

DELETE @tbl WHERE accum_width > 600

Note: this solution presumes SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]