MySQL select all rows from last N groups

Question

I have a dataset like this, where there can be multiple transactions per trade

| tx_id | trade_id |
--------------------
| 100   | 11       |
| 99    | 11       |
| 98    | 11       |
| 97    | 10       |
| 96    | 10       |
| 95    | 9        |
| 94    | 9        |
| 93    | 8        |
...

I want to select all of the transactions from the last N trades. For instance if I wanted to select all rows from the last 2 trades, I would get:

| tx_id | trade_id |
--------------------
| 100   | 11       |
| 99    | 11       |
| 98    | 11       |
| 97    | 10       |
| 96    | 10       |

I cannot guarantee that the trade_id will always have an interval of 1.

How can I accomplish this in mysql?

Answer 1

This will also work with mysql 5

Changing the linit , you can choose how many trades you want to receive

CREATE TABLE tab1 (
  `tx_id` INTEGER,
  `trade_id` INTEGER
);

INSERT INTO tab1
  (`tx_id`, `trade_id`)
VALUES
  ('100', '11'),
  ('99', '11'),
  ('98', '11'),
  ('97', '10'),
  ('96', '10'),
  ('95', '9'),
  ('94', '9'),
  ('93', '8');

SELECT t1.* FROM tab1 t1 JOIN (SELECT DISTINCT `trade_id` FROM tab1 ORDER BY `trade_id`  DESC LIMIT 2) t2
ON t1.`trade_id` = t2.`trade_id`

tx_id | trade_id
----: | -------:
  100 |       11
   99 |       11
   98 |       11
   97 |       10
   96 |       10

db<>fiddle here