CODEWITHSUNDEEP
sqlsqliteselect
Mrbiggerm
Mrbiggerm

Reputation: 155

Sqlite: Selecting records spread over total records

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!

Upvotes: 2

Views: 391

Answers (3)

Felix.leg
Felix.leg

Reputation: 769

In other words you want select rows with date column, so that date is from the sorted list of dates, from where we take every odd element? And add the last recorded element (with the latest date)? And everything limited to max 20 rows?

If that's the case, then I think this one should do:

SELECT id,value,date FROM source_table WHERE date IN (SELECT date FROM source_table WHERE (rowid-1) % 2 = 0 OR date = (SELECT max(date) FROM source_table) ORDER BY date) LIMIT 20

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

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)

Upvotes: 2

Rob Stevenson-Leggett
Rob Stevenson-Leggett

Reputation: 35689

I think you want this:

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

Upvotes: 0

Related Questions