Sqlite: Selecting records spread over total records

Question

I have a sql / sqlite question. I need to write a query that select some values from a sqlite database table. I always want the maximal returned records to be 20. If the total selected records are more than 20 I need to select 20 records that are spread evenly (no random) over the total records. It is also important that I always select the first and last value from the table when sorted on the date. These records should be inserted first and last in the result.

I know how to accomplish this in code but it would be perfect to have a sqlite query that can do the same.

The query Im using now is really simple and looks like this:

"SELECT value,date,valueid FROM tblvalue WHERE tblvalue.deleted=0 ORDER BY DATE(date)"

If I for example have these records in the talbe and to make an easier example the maximum result I want is 5.

id    value    date
 1     10       2010-04-10
 2      8       2010-04-11
 3      8       2010-04-13
 4      9       2010-04-15
 5     10       2010-04-16
 6      9       2010-04-17
 7      8       2010-04-18
 8     11       2010-04-19
 9      9       2010-04-20
 10    10       2010-04-24

The result I would like is spread evenly like this:

id    value    date
 1     10       2010-04-10
 3      8       2010-04-13
 5     10       2010-04-16
 7      8       2010-04-18
 10    10       2010-04-24

Hope that explain what I want, thanks!

Answer 1

I think you want this:

SELECT value,date,valueid FROM tblvalue WHERE tblvalue.deleted=0 
ORDER BY DATE(date), Random()
LIMIT 20

Answer 2

Something like this should work for you:

SELECT *
FROM (
    SELECT v.value, v.date, v.valueid 
    FROM tblvalue v
    LEFT OUTER JOIN (
        SELECT min(DATE(date)) as MinDate, max(DATE(date)) as MaxDate
        FROM tblvalue 
        WHERE tblvalue.deleted = 0 
    ) vm on DATE(v.date) = vm.MinDate or DATE(v.date) = vm.MaxDate
    WHERE tblvalue.deleted = 0 
    ORDER BY vm.MinDate desc, Random()
    LIMIT 20
) a
ORDER BY DATE(date)