I will make a sql statement that retrieves all the images as long as the sum of the size is 100
I have this:
SELECT PhotoNr
INTO # PhotoTabl
FROM Photo
WHERE Size <= 100????
ORDER BY PhotoOrder ASC
Table-Contents:
PhotoNr ...... Size
1 ............ 20
2 ............ 50
3 ............ 20
4 ............ 50
5 ............ 20
The sql will give the result:
PhotoNr ...... Size
1 ............ 20
2 ............ 50
3 ............ 20
Is there any good solution for this?
There are lots of ways to skin this cat. The best will depend on what's available to you, and the limitations imposed by size etc
One option would be to use a recursive CTE (this simple example assumes consecutive photonr values, non-consecutive could be allowed for if required):
;WITH CTE as (
select PhotoNr, Size, Tot = Size
from photos where photonr = 1
union all
select p.PhotoNr, p.Size, Tot = cte.Tot +p.Size
from CTE
join photos p on CTE.PhotoNr + 1 = p.photonr
)
select photonr, size from cte
where tot < 100
Alternatively a very simple way (to code) (but may not be so performant) could use cross apply:
select
photonr,
size
from photos p
cross apply (
select tot = sum(size) from photos sub
where sub.photonr <= p.photonr) x
where tot <= 100
PhotoNr is contiguous.If you are on SQL Server 2012+, there is an index with key column PhotoNr that includes Size and the number of rows expected to be returned is small relative to the number of rows in the table.
WITH P
AS (SELECT *,
SUM(Size) OVER (ORDER BY PhotoNr
ROWS UNBOUNDED PRECEDING) AS RunningCount
FROM Photo)
SELECT *
FROM Photo
WHERE PhotoNr < (SELECT TOP 1 PhotoNr
FROM P
WHERE RunningCount > 100
ORDER BY PhotoNr)
SELECT PhotoNr,Size from p where runningcount <= 100 will get the results
As @MartinSmith has said in the comments you just need to calculate a running total. There are many ways and it depends on you RDBMS as to what you can leverage to improve efficiency etc. One basic but pretty inefficient way is like this.
WITH cte AS
(
SELECT p1.id, p1.size, SUM(p2.size) running_total
FROM photos p1, photos p2
WHERE p1.id >= p2.id
GROUP BY p1.id, p1.size
)
SELECT id, size
FROM cte
WHERE running_total <= 100
There is an interesting looking article comparing methods for creating a running total here
PhotoOrderper theSELECTorPhotoNrper the example data?