Take the 2-minute tour ×

I have trouble putting together a delete statement in my Android application (I am using OrmLite).

I have a table filled with records. Two of the fields are "dateCreated" (type Date) and "imageSize" (type int). In my code I have a method free(int size). This method tells me that I have to delete oldest records from my table that sum "imageSize" <= size.

For instance .. i get parameter 1000. Each record has value lets say 100. That means i have to delete 10 of the oldest records.

Can some one please provide me with optimal raw SQL statement or even better an OrmLite code for this?

I would be most gratefull.

share|improve this question
So if each image has a size of 100 and you say free(1000), then all images will match, right? Where does the 10 come in? Is that a constant? –  Gray Jul 6 '11 at 14:29
Any feedback on my answer @no9? –  Gray Sep 7 '11 at 12:22
sorry Gray i havent received a notification on this anwser. Yes it was a typo. –  no9 Oct 14 '11 at 9:05
add comment

2 Answers

up vote 1 down vote accepted

Unfortunately, you can't do this with a single SQL statement. There is no way to say

select records until their sum is less than X

You could doing multiple queries until you found the oldest records whose sum is less than X but it would take a number of separate SQL calls.

I'd recommend selecting the last X images with their sizes and then doing a delete of the right number of images using a DELETE ... IN .... Here's the pseudo code:

while (true) {
   SELECT id, imageSize FROM yourtable ORDER BY dateCreated DESC LIMIT 10;
   find the images from the bottom whose sum(imageSize) <= parameter
   delete the found images
   break if you exceed the parameter otherwise loop and get the next 10
share|improve this answer
this might do the trick, i have implemented something similar and it is consuming alot of resources, will use this solution instead –  no9 Oct 14 '11 at 9:06
add comment

try this,

DELETE FROM yourtable WHERE imageSize <= (SELECT SUM(ImageSize) FROM yourtable)

Use the same parameter for your function

share|improve this answer
you did not understand my question. I need to delete oldest records that their SUM (image size) is <= "size" –  no9 Jul 6 '11 at 11:09
@no9 I have updated the query,hope this is the query you needed –  sudheshna Jul 6 '11 at 11:40
add comment

Your Answer


