MySQL select all rows from last N groups-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You use DENSE_RANK on trade_id descending, then filter on your required X for "last X":

CREATE TABLE t (tx_id int, trade_id int);

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

SET @ngroups=2;

WITH dat
AS
(
SELECT tx_id, trade_id, DENSE_RANK() OVER (ORDER BY trade_id DESC) AS trade_id_rank
FROM t
)
SELECT tx_id, trade_id
FROM dat
WHERE trade_id_rank <= @ngroups;

dbfiddle.uk

CodePudding user response：

If we assume the "last trades" are the ones with the highest trade_id numbers, then you can use DENSE_RANK().

For example:

select *
from (
  select *,
    dense_rank() over(order by trade_id desc) as dr
  from t
) x
where dr <= 2

CodePudding user response：

This can be done with a CTE

WITH trades AS
SELECT trade_id tid
FROM myTable
GROUP BY trade_id
ORDER BY trade_id
LIMIT 2
SELECT * FROM
trades 
JOIN myTable ON trade_id = tid
ORDER BY tx_id;