Find "old" rows

Question

Consider a table like this:

| txn_id | account_id

I'd like to do a single query that will get me all txn_ids for all transactions where the transaction is not the most recent (highest txn_id) for the account_id .

The database is MySQL 5.1, so that might imply some limitations around sub-selects.

Answer 1

If I understand right

SELECT txn_id
  FROM table
 WHERE txn_id <> (SELECT MAX(txn_id) FROM table WHERE account_id = 123456)
   AND account_id = 123456

Answer 2

Try something like this:

select txn_id, account_id
order by txn_id desc
limit 1,18446744073709551615;

Answer 3

select txn_id from table 
where account_id = 123 and 
txn_id < (select max(txn_id) from table where account_id = 123)

Answer 4

Given the requirement:

I'd like to do a single query that will get me all txn_ids for all transactions where the transaction is not the most recent (highest txn_id) for the account_id.

it seems that answers which give you transaction IDs for a single account at a time are missing the point of the query.

To get the list of transaction IDs that must be retained (or ignored) we can write:

SELECT MAX(txn_id) AS max_txn_id, account_id
  FROM UnnamedTable
 GROUP BY account_id;

Now we need to get the list of transaction IDs not found in that list. That isn't wholly straight-forward:

SELECT txn_id, account_id
  FROM UnnamedTable
 WHERE txn_id NOT IN
             (SELECT max_txn_id
                FROM (SELECT MAX(txn_id) AS max_txn_id, account_id
                        FROM UnnamedTable
                       GROUP BY account_id
                     )
             )

Anyway, that works OK with IBM Informix Dynamic Server 11.50 and the table and data below:

create table unnamedtable(txn_id integer not null, account_id integer not null);
insert into unnamedtable values(1, 12);
insert into unnamedtable values(2, 12);
insert into unnamedtable values(3, 12);
insert into unnamedtable values(4, 13);

yielding the results:

1    12
2    12