How to show multiple record if has top 1

Question

Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-05.

Table 1

Key 2 Name

---

78     Test 1 
222    Test 2            
824    Test 3  
1980   Test 4
3747   Test 5

Please see image for table 2

select top 1 (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc

my query only getting one record i want to get all the record on key 2

Answer 1

Top is only giving you one record because you're asking for the top '1' row. If you want all, just change it to:

select (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc