A difficult SQL question (I am using postgres incidentally).

I want the very first row placed every single day within the last X days. among my posts is really a timestamp, that we contain the time placed, and the other column may be the row id.

If you cannot obtain the first row placed every single day, i a minimum of require a unique one just one row for each day within the last x days.

Any suggestions?

Thanks

okie

You might want to try something similar to the next (examined in MySQL, however i guess it ought to be simple 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);

Observe that this assumes that the timestamps are unique.

Test Situation (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)

mr vassallo, you are a rock star.

it labored great. this is actually the postgres version of the 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's you don't need to obtain the minimal ID because i am unable to be guaranteed the card inserts come in direct chronological order (the timestamp isn't the placed time, but a timestamp dwelling inside the data, and data packets can emerge from order).

i truly appreciate the assistance. appreciate considering this.