SQL - First row inserted every day for the past X days

Question

A tough SQL question (I'm using postgres by the way).

I need the first row inserted every day for the past X days. one of my columns is a timestamp, which i hold the time inserted, and another column is the row id.

If it's not possible to get the first row inserted every day, i at least need a unique one; a single row for every day for the past x days.

Any suggestions?

Thanks

okie

Answer 1

You may want to try something like the following (tested in MySQL, but I guess it should be easy to port to Postgres):

SELECT      l.id, l.timestamp, l.value
FROM        log l
INNER JOIN  (
             SELECT    MIN(timestamp) first_timestamp
             FROM      log
             GROUP BY  DATE(timestamp)
            ) sub_l ON (sub_l.first_timestamp = l.timestamp)
WHERE       l.timestamp > DATE_ADD(NOW(), INTERVAL -30 DAY);

Note that this assumes that your timestamps are unique.

Test Case (in MySQL):

CREATE TABLE log (id int, timestamp datetime, value int);

INSERT INTO log VALUES (1, '2010-06-01 02:00:00', 100);
INSERT INTO log VALUES (2, '2010-06-01 03:00:00', 200);
INSERT INTO log VALUES (3, '2010-06-01 04:00:00', 300);
INSERT INTO log VALUES (4, '2010-06-02 02:00:00', 400);
INSERT INTO log VALUES (5, '2010-06-02 03:00:00', 500);
INSERT INTO log VALUES (6, '2010-06-03 02:00:00', 600);
INSERT INTO log VALUES (7, '2010-06-04 02:00:00', 700);
INSERT INTO log VALUES (8, '2010-06-04 03:00:00', 800);
INSERT INTO log VALUES (9, '2010-06-05 05:00:00', 900);
INSERT INTO log VALUES (10, '2010-06-05 03:00:00', 1000);

Result:

+------+---------------------+-------+
| id   | timestamp           | value |
+------+---------------------+-------+
|    1 | 2010-06-01 02:00:00 |   100 |
|    4 | 2010-06-02 02:00:00 |   400 |
|    6 | 2010-06-03 02:00:00 |   600 |
|    7 | 2010-06-04 02:00:00 |   700 |
|   10 | 2010-06-05 03:00:00 |  1000 |
+------+---------------------+-------+
5 rows in set (0.00 sec)

Answer 2

mr vassallo, you're a rock star.

it worked great. here is the postgres version of your SQL:

SELECT l.id, l.timestamp, l.value
FROM log l
INNER JOIN  (
             SELECT    MIN(timestamp) AS first_timestamp
             FROM      log
             GROUP BY  DATE(timestamp)
) sub_l ON (sub_l.first_timestamp = l.timestamp)
WHERE       l.timestamp > NOW() - INTERVAL '30 DAY' ORDER BY l.timestamp;

there is no need to get the minimal ID because i cannot be guaranteed that the inserts will be in direct chronological order (the timestamp is not really the inserted time, but a timestamp residing within the data, and data packets can come out of order).

i really appreciate the help. thank you for taking a look at this.