-1

Table1: https://i.stack.imgur.com/XVyYf.png

Table2: https://i.stack.imgur.com/762uM.png

expect the result: https://i.stack.imgur.com/lNssy.png

if have many data in array[R80,R01,R02,R03,R04,R05...] and 'Table2', so i want in array to find the 'Table2' data the result must 'date' is new(DESC)/limit 1

example:

like select * from tabel2 where ID = 'R80' DESC Date Limit1

But i want to get all items [R80,R01,R02,R03,R04,R05...]

like select * from tabel2 where in [R80,R01,R02,R03,R04,R05...] DESC Date Limit1??

Please help~Thx

not use "loop,declare,@" is best

Please dont answer 
select * from tabel2 where ID = 'R80'...;
select * from tabel2 where ID = 'R01'...;
select * from tabel2 where ID = 'R02'...;
                   ...
MT0
  • 143,790
  • 11
  • 59
  • 117
HF Lx
  • 13
  • 4
  • What's your question about this? Anything not working with `WHERE ID in [a,b,c]`? – Nico Haase Jan 06 '22 at 12:40
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product (and not all support arrays). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jan 06 '22 at 12:57

1 Answers1

0

Seems like you are looking for top 1 row for all IDs based on latest date. Use row number

SELECT * FROM (
    --mark 1st row as 1 for every ID
    SELECT *,row_number() over(partition by ID order by date desc) RN from table2
    where id in ('R80','R01','R02','R03','R04','R05') 
) INNER_QUERY 
WHERE RN = 1
Equinox
  • 6,483
  • 3
  • 23
  • 32