"John" <topguy75@hotmail.com> wrote in message
news:4378f93a$0$23294$db0fefd9@news.zen.co.uk...
> Can someone please help
>
> I have two tables:
>
> 1.Project Table that holds a Unique Project No
>
> 2. Quotes table that holds the Project No, Quote Number and Creation date
> and quote value. There are multiple quotes for each project.
>
> I want to return Project No and one quote Value for each Project with the
> earliest creation date.
>
> Regards
>
> John
>
>
>

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OuxmQ5V6FHA.476@TK2MSFTNGP15.phx.gbl...
> Try:
>
> select
> p.*
> , q.CreationDate
> , q.Value
> from
> Projects p
> join Quotes q on q.ProjectNo = q.ProjectNo
> join
> (
> select
> ProjectNo
> , min (CreationDate) CreationDate
> from
> Quotes
> group by
> ProjectNo
> ) x on x.ProjectNo = p.ProjectNo
> and x.CreationDate = q.CreationDate
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
>
> "John" <topguy75@hotmail.com> wrote in message
> news:4378f93a$0$23294$db0fefd9@news.zen.co.uk...
>> Can someone please help
>>
>> I have two tables:
>>
>> 1.Project Table that holds a Unique Project No
>>
>> 2. Quotes table that holds the Project No, Quote Number and Creation date
>> and quote value. There are multiple quotes for each project.
>>
>> I want to return Project No and one quote Value for each Project with the
>> earliest creation date.
>>
>> Regards
>>
>> John
>>
>>
>>
>
>