Need help with SQL statement - aggregate function in joined table

Posted on 2005-05-04
Last Modified: 2010-03-19
I have two tables:
Table1 with columns:

Table2 with columns

There is a 1->many relationship between table 1 and table 2

I need a query that looks like this:
ID   Date1   Date2  Max(Date3)

So far I haven't quite gotten this to work:

select, a.date1, a.date2, b.date3
table1 a, (select loan, max(date3) from
(select * from table2 c where = ) b
where =
Question by:WhidbeyIslndr
    LVL 4

    Assisted Solution

    select, a.date1, a.date2, max(b.date3)
    from table1 a, table2 b
    where =
    group by, a.date1, a.date2
    LVL 14

    Accepted Solution

    Hi WhidbeyIslndr!

    You can also do it this way :

    Select a.Loan, a.Date1, a.Date2, Max(b.Date3)
    From table1 a
    Inner Join table2 b
      On b.Loan = a.Loan
    Group by a.Loan, a.Date1, a.Date2

    * Note : If you need to return those records without max(Date3) then use "Left Join" instead of "Inner Join".

    Hope this helps you.  Just try it.

    eNTRANCE2002 :-)

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now