Mysq/Mariadb get newest entry for some ids in a IN Select group by id-CodePudding

Hello i have a table with some ids and values

for example:

SELECT instrumentid, value  from `mytable` where instrumentid in (12,11, 14,15);

id, instrumentid, recorddate, value
33  12            2022-10-05   55
34  11            2022-10-05   33
30  14            2022-10-05   13
29  12            2022-10-03   12
28  11            2022-10-03   53
40  14            2022-10-03   4
44  15            2022-10-03   4

as result i want or better explained only the last newst entry for instrumentid

instrumentid, value 
    12 55
    11 33
    14 13
    15 4

thanks and regards

running latest mariadb 10.9.3

CodePudding user response：

You can use ROW_NUMBER() to identify the last row for each instrument.

For example:

select *
from (
  select t.*,
    row_number() over(partition by instrumentit order by recorddate desc) as rn
  from mytable t
  where instrumentid in (12,11, 14,15)
) x
where rn = 1