At 11:17 AM 11/08/2004 +0000, you wrote:

>Hi All, I would like to know if it is possible to write a select
>query to be able to retrieve records from a single table where the
>sum of the records does not exceed a certain amount. I know this
>query is not going to work, but just to explain my problem to you I
>will use this as an example...
>SELECT UniqueID, MyLength
>FROM MyTable
>WHERE SUM( MyLength ) < 500
>
>The result should be multiple records where the sum of all the
>records returned does not exceed 500. If this is not possible by
>using a select query, I would appreciate any input on how to
>accomplish this without retrieving all the records from the database.

I think you simplified your example too much!!

If UniqueID represents a unique key, then your query is illogical. SUM()
operates on groups of records, so SUM() needs to be grouped on a column
that represents some kind of grouping. If you grouped by the UniqueID, the
SUM() would represent exactly one record.

For an example of grouping, let's say you have a column COLOUR in this
table. There are many items in the table of each colour. Then, you could
get a list of all the colours for which the aggregated length is less than 500:

SELECT COLOUR
FROM MyTable
GROUP BY COLOUR
HAVING SUM(MyLength) < 500

If you want a (sloooooow!!) listing of all of the UniqueIDs that are in the
colour groups for which the sum of the lengths doesn't exceed 500:

SELECT mt1.UniqueID, mt1.MyLength /*, mt1.COLOUR if you want it */
FROM MyTable mt1
WHERE mt1.COLOUR IN (
SELECT mt2.COLOUR FROM MyTable mt2
GROUP BY mt2.COLOUR
HAVING SUM(mt2.MyLength) < 500)

Or maybe you had something entirely different in mind. :-)

/heLen