Posted by Ed Murphy on 11/30/06 11:27

Douglas wrote:

> 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 :(
>
> if maxwidth=600
>
> image1 could be 500 - running total=(500)
> image2 could be 50 - (500+50=550, no fail)
> image3 could be 75 - (550+75=625) Failcount = 1, try again 4 more times
> image4 could be 250 - (550+250=800) Failcount = 2, try again 3 more times
> image5 could be 50 - (550+50=600, no fail (600 is not > 600 ;))
> image6 could be 75 - (600+75=675) Failcount = 3, try again 2 more times
> image6 could be 75 - (600+500=1100) Failcount = 4, try again 1 more times
> image6 could be 75 - (600+250=850) Failcount = 5, stop.
>
> So, requesting a FINITE qty of images will not meet the needs of the goal.
>
> The goal is: to get as many pictures, as close to a total width of 600
> as possible. (without causing server meltdown, of course ;)

Ah, much clearer than the original spec, thank you.

> > (Insert standard Celko "rows vs. records" rant here.)
>
> I'd like to know more about that... although, now that I have asked, I
> may live to regret it.

This pertains to all the people accustomed to e.g. Access, where
each table's records have row numbers, and default to sorting in
that order. Then they encounter any number of things in SQL that
look deceptively similar: primary keys, auto-assigned ID numbers,
or just a query without an ORDER BY that happens (this time!) to
return data in the same order it was inserted. Not to mention
views and ORDER BY (see http://www.devx.com/dbzone/Article/8048).

There's a parallel "columns vs. fields" rant - I think it pertains
to inappropriate data types and/or lack of proper check constraints.

> > http://www.sqlteam.com/item.asp?ItemID=3856
>
> i'm looking into that...

Yeah, I think it'll adapt well enough to what you described above.

[Back to original message]