Query help

dgrafx
dgrafx used Ask the Experts™
on
I have table_one
id(int), name(varchar)

example recordset from table_one
1000 Testone
1001 Testtwo
1002 Testthree

joined on table_two
id(int), sdate(date), edate(date)

example recordset from table_two
1000 01/12/2019     02/15/2019
1000 02/16/2019     03/15/2019
1000 03/16/2019     04/19/2019
1002 01/16/2019     02/13/2019
1002 02/14/2019     04/17/2019

This is a one to many relationship with table_one being primary
I want to select only one row from table_two that has the latest edate value

So result set of desired query would be

1000 Testone 03/16/2019     04/19/2019
1002 Testthree   02/14/2019     04/17/2019

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT t1.*, oa1.sdate, oa1.edate
FROM dbo.table_one t1
OUTER APPLY (
          SELECT TOP (1) *
          FROM dbo.table_two t2
          WHERE t2.id = t1.id
          ORDER BY edate DESC
) AS oa1
Hold on a minute - I'm having some issues ...
Will post back in a bit
OK works great Scott!
Thanks!
!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial