Select all items with the sum of the fields in the specified range.

Question

I have a simple table:

file_size file_id file_time 1 1 19 2 2 20 3 3 21 4 4 22 5 5 23 

I want to find an element such that all elements with fewer file_time files have the sum of file_size in a predetermined range. I wrote the following query:

 SELECT * FROM test_table AS D0 WHERE (SELECT TOTAL(file_size) FROM test_table AS D1 WHERE D1.file_time <= D0.file_time ORDER BY file_id) BETWEEN 1 AND 9 

This query will get the correct results:

  1 1 19 2 2 20 3 3 21 

But this request does not work if the necessary elements have the same file_time field:

 file_size file_id file_time 1 1 20 2 2 20 3 3 20 4 4 20 5 5 20 

Desired result for this data:

  1 1 20 2 2 20 3 3 20 

The file_id field is unique. What is wrong with my SQL query?

Code to create a test pattern:

 CREATE TABLE test_table (file_size INT, file_id INT, file_time INT) INSERT INTO test_table VALUES(1,1,20) INSERT INTO test_table VALUES(2,2,20) INSERT INTO test_table VALUES(3,3,20) INSERT INTO test_table VALUES(4,4,20) INSERT INTO test_table VALUES(5,5,20) 

Answer 1

You should not consider file_time as one column in your query, since you also want to consider file_id column. You should use the file_time and file_id , and you should compare them lexicographically as follows:

 SELECT * FROM test_table AS D0 WHERE ( SELECT TOTAL( file_size ) FROM test_table AS D1 WHERE D1.file_time < D0.file_time OR ( D1.file_time = D0.file_time AND D1.file_id <= D0.file_id ) ORDER BY file_time, file_id DESC ) BETWEEN 1 AND 9 

Answer 2

Not sure if I understood, but I think

 -- sum of file sizes between 1 and 7 with the lowest time SELECT SUM(test.file_size) AS sum_file_size, test.file_time FROM test WHERE (test.file_time = (SELECT TOP 1 test.file_time FROM test ORDER BY file_time)) AND (test.file_size BETWEEN 1 AND 9) GROUP BY test.file_time; -- sum of file sizes per time `group` SELECT SUM(test.file_size) AS sum_file_size, test.file_time, FROM test WHERE (test.file_size BETWEEN 1 AND 7) GROUP BY test.file_time ORDER BY test.file_time;