0

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 2enter image description here

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

  • 1
    I guess that removing the TOP (1) doesn't give you what you want? If you mock up a result set of what you are looking for, we can probably guide you better, right now it's hard to tell what your objective is. – Jonathan Fite Jan 21 at 13:43
  • Are you willing to get the top 1 for each Key2? – Ronaldo Jan 21 at 14:59
  • 1
    "I want to get all records", but your query is SELECT TOP 1 .... Isn't it obvious that the TOP 1 is causing it not to display all records? Have you tried removing the TOP 1? – Eric Jan 21 at 18:31
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.