Query Help

11 Nov 2005 5:32 PM

dotnettester

Hi,

I have two tables
Products
----------
ProductId
ProductTitle

Bids
-----
BidId
ProductId
BidAmount
BidderName

I want to grab all the products with the highest bidAmount and the BidderName.
There could be multiple bidders for each product.

12 Nov 2005 1:53 AM

Tom Moreau

Try:

select
    p.ProductID
,    p.ProductTitle
,    b.BidID
,    b.BidAmount
,    b.BidderName
from
    Products    p
join
    Bidders    b    on    b    b.ProductID    = p.ProductID
where
    b.BidAmount =
(
    select
        max (b2.BidAmount)
    from
        Bidders    b2
    where
        b2.ProductID    = p.ProductID
)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

Show quote

"dotnettester" <dotnettes***@discussions.microsoft.com> wrote in message
news:D60ADC91-6F30-49EE-A50C-63E3CAF9649A@microsoft.com...
> Hi,
>
> I have two tables
> Products
> ----------
> ProductId
> ProductTitle
>
> Bids
> -----
> BidId
> ProductId
> BidAmount
> BidderName
>
> I want to grab all the products with the highest bidAmount and the
> BidderName.
> There could be multiple bidders for each product.
>