SQL: Need to limit the resultset based on a subquery

Answer 1

query I'm suggesting:

SELECT *
FROM packets
WHERE total = ( SELECT total
    FROM packets
    WHERE timestamp = ( SELECT MAX(timestamp) FROM packets ))
 AND timestamp >= ( SELECT MAX(timestamp) FROM packets WHERE idx = 1 )
ORDER BY timestamp DESC;

Inaction:

mysql> create table packets( id bigint(20) AUTO_INCREMENT primary key, data char(1), idx int(10), total int(10), timestamp datetime );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into packets( data, idx, total, timestamp ) values( 'a', 1 ,2,'2009-01-02 01:01:32'),
    ->     ('b' ,2 ,2,'2009-01-02 01:03:32'),
    ->     ('c'  ,1 ,3,'2009-01-03 01:01:32'),
    ->     ('d'  ,2 ,3,'2009-01-03 01:04:32'),
    ->     ('e' ,3 ,3,'2009-01-03 01:02:32'),
    ->     ('f' ,1 ,2,'2009-01-05 01:01:32');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM packets
    -> WHERE total = ( SELECT total
    -> FROM packets
    -> WHERE timestamp = ( SELECT MAX(timestamp) FROM packets ))
    -> AND timestamp >= ( SELECT MAX(timestamp) FROM packets WHERE idx = 1 )
    -> ORDER BY timestamp DESC;
+----+------+------+-------+---------------------+
| id | data | idx  | total | timestamp           |
+----+------+------+-------+---------------------+
|  6 | f    |    1 |     2 | 2009-01-05 01:01:32 |
+----+------+------+-------+---------------------+
1 row in set (0.00 sec)

mysql> delete from packets where id = 6;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM packets WHERE total = ( SELECT total FROM packets WHERE timestamp = ( SELECT MAX(timestamp) FROM packets )) AND timestamp >= ( SELECT MAX(timestamp) FROM packets WHERE idx = 1 ) ORDER BY timestamp DESC;
+----+------+------+-------+---------------------+
| id | data | idx  | total | timestamp           |
+----+------+------+-------+---------------------+
|  4 | d    |    2 |     3 | 2009-01-03 01:04:32 |
|  5 | e    |    3 |     3 | 2009-01-03 01:02:32 |
|  3 | c    |    1 |     3 | 2009-01-03 01:01:32 |
+----+------+------+-------+---------------------+
3 rows in set (0.00 sec)

mysql>

Answer 2

If they arrive in order without other packets being written in between, following should also work.

SELECT *
FROM Total t
     INNER JOIN (
       SELECT Total, Timestamp
       FROM Total t
            INNER JOIN (
              SELECT Timestamp = MAX(Timestamp) 
              FROM Total
              WHERE ID = 1
            ) ts ON ts.Timestamp = t.Timestamp.
     ) tit ON tit.Total = t.Total AND tit.Timestamp <= t.Timestamp

Answer 3

This is how I'd do it in sql server, you can convert to mysql syntax.

SELECT *
FROM table
     INNER JOIN (SELECT TOP 1 * FROM table ORDER BY key DESC) AS t ON (table.timestamp = t.timestamp AND table.total = t.total)

Answer 4

I ended up going with a slightly different query form:

CREATE VIEW NewestTimestamps AS
  SELECT index, MAX(timestamp) AS maxTS FROM table GROUP BY index;

CREATE VIEW NewestList AS
  SELECT * FROM table AS t
    JOIN NewestTimestamps sub ON t.timestamp = sub.maxTS AND sub.index = t.index
  WHERE t.total = (SELECT t2.total FROM table AS t2 
    WHERE timestamp = (SELECT MAX(timestamp) FROM table)
  ); 

This list is not precisely what I asked for, but it seems actually impossible to distinguish newer and older data reliably. Instead, this will give me the newest element at index 1, then index 2, etc... Additionally, the WHERE clause will limit the size of the view to the size of the most recently arrived queue.

Note that the first view is required since mysql does not allow subqueries in the FROM clause in a view.