Sqlite: selecting records by shared records
I have a sql / sqlite question. I need to write a query that fetches some values from a sqlite database table. I always want the maximum records returned to be 20. If the total selected records are greater than 20, then I need to select 20 records that are distributed evenly (no random) across the total records. It is also important that I always select the first and last value from the table when sorting by date. These records should be inserted first and last in the result.
I know how to do this in code, but it would be ideal if the SQL query could do the same.
Now using the request Im very 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 entries in talbe and to make the example simpler, 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 evenly distributed 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 I can explain what I want, thanks!
a source to share
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)
a source to share