Get latest row in database, each grouped by value-CodePudding

In MySql i have a table 'test' with these columns

|id (int)|merchant (int)|eta (date)|FK (int)|

I want the returned results to be grouped by FK. So the latest (highest) id is returned for each row (just 1 column for each).

So in this table

--------------------
|id|merchant|eta|FK|
|1|1|2022-11-07|1920|
|2|2|2022-11-08|1920|
|3|1|2022-11-09|1920|
|4|3|2022-11-07|1921|
|5|1|2022-11-07|1921|
|6|1|2022-11-07|1920|

I want these rows

|5|1|2022-11-07|1921|
|6|1|2022-11-07|1920|

The last query i tried is this

SELECT
id, merchant, eta, FK
FROM test   
GROUP BY FK ORDER BY id DESC

But this is returning

|4|3|2022-11-07|1921|
|1|1|2022-11-07|1920|

Mysql for debugging:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `merchant` int(11) NOT NULL,
  `eta` date NOT NULL,
  `FK` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `test` (`id`, `merchant`, `eta`, `FK`) VALUES
(1, 1, '2022-11-07', 1920),
(2, 2, '2022-11-08', 1920),
(3, 1, '2022-11-09', 1920),
(4, 3, '2022-11-07', 1921),
(5, 1, '2022-11-07', 1921),
(6, 1, '2022-11-07', 1920);

CodePudding user response：

This can be done simply by using ROW_NUMBER , but it requires MySQL 8

select id, merchant, eta, FK
from (select *,
             row_number() over(partition by FK order by id desc) as row_num
      from test
    ) t
where row_num=1
  ;

https://dbfiddle.uk/_4zII4Vx

On older MySQL versions a subquery would do the trick:

select t.id, 
       t.merchant, 
       t.eta, 
       t.FK
from test t 
inner join (select max(id) as max_id,
                   FK
            from test
            group by FK
           ) as tbl on tbl.max_id=t.id;

https://dbfiddle.uk/rQe4uOwU