mardi 25 août 2015

How to select array of rows with max param from each range?

I have sqlite table like so:

CREATE TABLE "table" (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    `param` REAL NOT NULL,
    `date`  INTEGER NOT NULL
);

INSERT INTO table (param , date) VALUES (123.3, 1427824800 );
INSERT INTO table (param , date) VALUES (122.3, 1427825800 );
INSERT INTO table (param , date) VALUES (125.0, 1427652000 );
INSERT INTO table (param , date) VALUES (123.9, 1427652900);


|id| param | date       |
|==|=======|============|
| 1| 123.3 | 1427824800 |
| 2| 122.3 | 1427825800 |
| 3| 125   | 1427652000 |
| 4| 123.9 | 1427652900 |

And get row with max date like

SELECT id, param, MAX(date) FROM table WHERE date BETWEEN 1427652000 AND 1427824799
SELECT id, param, MAX(date) FROM table  WHERE date BETWEEN 1427824800 AND 1427911199

But trully I got much more data for nearly year and 1000+ rows and make 365 queries not an option I think, but I don't know how to optimize it

UPD After all I think it is real to get one query to get result like this:

|id| param | date       |
|==|=======|============|
| 2| 122.3 | 1427825800 |
| 4| 123.9 | 1427652900 |

Aucun commentaire:

Enregistrer un commentaire